Advanced Formulas & Macros

23. The INDEX Function

Next up is the index function, which is rarely used by itself in Excel as it's usually combined with the match function, but it's still important to understand how it works. The index function returns the value of a cell at the intersection of a particular row and column in a range. Range or ranges? Syntax wise, we have a few arguments. The reference is the cell range that contains the value or values you need. And starting at the top of that range, the row number is the number of rows down, the value you want is, and the column number is how many columns over the value you want is.

Finally, if you entered more than one cell range in the reference, you need to specify which one to use in the area number. As an example, consider this pizza menu, where we have different pizzas and sizes with the price for each. And if we wanted to obtain the price of an average Italian pizza, we could use the following index function: So the index for the reference is going to be one to five. The row number is four, since the Italian pizza is in the fourth row of our range, and the column number is three, since the medium size is in the third column. Since we only have one range, we do not need to enter an area number. So the result would be $12.99.

Now, if we did have two ranges, so maybe a pizza and a calzone menu, and we wanted to obtain the price of a medium Italian calzone, then we would use the following index function: So index, and then the references are 1–5, and 7–11, seven to d eleven. The row number and column numbers are still four and three, but since we have an area number of two, it will pull the data from the calzone menu or our second range, and the result would now be $10.99. Important note: when using more than one reference, make sure to wrap them in parentheses.

Now, I know that it seems like an overly complicated way of pulling the price here since we could just reference that sale directly, but as we go over the match function in the next lecture and the use of both together, it will make more sense. So I just wanted to jump right into Excel and do a quick demonstration of the index function. Now, I'm going to do it using the host dashboard information in the host tab, but I'm not actually going to save any of this. So if you already understood the index function from the slides, you can go ahead and skip to the next lecture. But what I want to do is pull the number of reviews for the private room from this range, so we can use the index function to do that. So I'm just going to open up the index, open that up. My array is going to be this range of cells, so R 4 to T 7. The row number is going to be 1, 2, 3. And the column number is going to be one, because we want this number. So 17. Close that. Since we don't need to add another area number, press Enter. And there we go. So there are 17 reviews for a private room, and that's really all there is to the index function.

24. The MATCH Function

Next up is the index function's partner in crime, the match function. The match function returns the relative position of a specified value within a one dimensional array. And syntax wise, it's very similar to a V lockup or H lookup function. We have the lookup value, which is the value you're trying to find in the position of the lookup array, which is the row or column in which you are looking for that value, and the match type. although here we have three options. So zero is still for matching an exact value, which will still take 99% of the time.

One, which is still the default, will look for the largest value less than or equal to the lookup value, and the negative one will look for the smallest value greater than or equal to the lookup value. Now, looking at the pizza example, if you wanted to know the position of the Italian pizza in column A, we could use a match function. So match, our lookup value is Italian, and we will look for it in the range of one to five. So we have column A, and we want an exact match. So we'll use a match type of zero. Then Excel will look for Italian in this row and find that it's in the fourth position. And if we wanted to know the position of the price 1299 in the fourth row here for Italian, well, we can use the match function as well. So matching, our lookup value is now 1299. in sells a four to a D four. So row four, and we want to find an exact match. So the result of three tells us that 1299 is in the third position in that row, and that's it for the match function. You may already be identifying the way in which this is complementary to the index function. And for now, we're going to see a quick example in Excel of how the match function works. But if you've already got it down, you can go ahead to the next lecture, where we'll be using index and match together.

So we're here in Excel for a really quick showcase of how the match function works. And I'm going to be using the host dashboard from our host tab, although I'm not actually going to save the changes. So let's say we wanted to know the position in which our private room lands in these headers for our table. Well, to do that, we can use the match function. Open that up. Our lookup value is going to be private room, so we have to write that between quotation marks. Our lookup array, which, remember, needs to be one dimensional, So either a column or a row can't be a two-dimensional array. In this case, we want the header row because this is where we want to find our private room. And the match type is going to be zero, since we want an exact match.

Close that, and we get a result of 2, which means private room is in the second position in these headers, which of course is correct. Now, if we wanted to do that but for a column, say we wanted to know the position in which the 17 is in this column, well, we can do the same thing. So match. Open that up. Our lookup value is going to be 17, and we want to find it in this column again, one dimensional. We want an exact match. Close that, and you'll see we get a date here because the cell is formatted as a date, but we can just move it to general, and you'll see that that date state value is what we were looking for. So the number three tells us that in this column, number 17 is in the third row down, which again is correct. So that's all there is to it. Now it's time to put index and match together to take full advantage of both of these functions.

25. Combining INDEX & MATCH

We're finally here to index and match. Index and match are commonly used together to act as a versatile lookup function that can find values in any row or column in an array. And what this means is that your lookup value no longer needs to be in the leftmost column or in the top row, which makes this a more powerful combination than simply using VLOOKUP or HLOOKUP. So, let's look at an example. Consider this pizza price calculator. And what we want is to be able to select a pizza and the size, and for it to automatically pull the price from our pizza menu. So, to do that, we can use this combination of index and match functions. Now, I know it's a little overwhelming at first glance, but let's take a look at what we have. So, we start with an index function, and the reference for that index function is range B 9 to D 12. The row number for that index function is actually a match function, as is the column number. So what is Excel actually doing here?

Well, when Excel evaluates this formula, it does so from the inside out, in this order. So, we start with the first match function, which is match B 3 in the range of nine to twelve. and we're using an exact match. But what Excel is reading here is this: We need to match pepperoni since the value of B-3 is pepperoni in this range. and we need to return its position, which in this case is two. Now, for the next match function, we're matching large, since the value of B 4 is large and in the range B 8 to D 8. And we're also returning it to its original position, which in this case is three. And now that we know the result of the eachmatch function, our index function is simplified to this. So, index, our reference is still the range B1 to D12, which is our price range. And we want to extract the value in the second row down and the third column over, which is our price of 1499. Pretty cool, right? So, to summarize, let's think about what each function is doing here.

The index function returns a value within an array, in this case, the prices, as long as you tell it what row and column it lives in. So, it's not much use by itself. But what the match functions are doing here is telling the index function what row and column to use based on the lookup values that you're providing, which in this case are the pizza and the size. Now, protip, Excel recently introduced the XLOOKUPfunction, which can operate like index and match but introduces several new powerful options like wildcard matching, searching from the bottom, and a value of "not found." Now, this function isn't part of the exam topics, which is why we won't cover it in this course. But if you're interested, Kris has some very cool demos in our formulas and functions course from Maven Analytics. But enough of that. Let's roll up our sleeves, head over to Excel, and use index and match together in our course project. Here in Excel We're back in our Places tab in our course project workbook. And what we've done with lookup functions thus far is pull information from our host table using our host ID and the VLOOKUP function. And we did the same with our Neighborhood tab and our Neighborhood ID, again using the VLOOKUP function. But if you recall, when we dragged these formulas to the right, what we had to do was modify our column index number so that it would move over to the right along with our formulas.

I'm going to press CTRL Z here, and even though it wasn't the most annoying thing in the world, it is an extra step that if we forget, then it may lead to some errors when working with our data. So what we're going to do in this lecture is replace these VLOOKUP functions with index and match combinations that will allow us to write a single formula right here in C-2 and be able to drag that across and drag it down and still obtain all the correct results, and then do the same with our districts and neighborhoods. So let's delete what we have right now and start with our index function. And as a refresher, you'll see that index returns a value or reference of the cell at the intersection of a particular row and column in a given range.

So our first argument is our array, or the range that they're asking for, which in this case is going to be our host table. So we know that this is columns A to M, and we don't make any mistakes with the reference types this time. Let's think about whether we need to fix this or not. So as we drag our formula to the right in our places, we don't want these columns to move over. We always want them to be A to M. So we do want to fully fix this. I'm going to press F 4, and we're going to comment over to our row number in our index function. And what row from this table do we want? Well, that's going to depend on the host ID, which is why we need to use a match function.

So a match function, again, as a refresher, returns the relative position of an item in an array that matches the specified value in the specified order. So in this case, if we open that up, the value we want to look for in this host table is the host ID that we have in our Places table. And again, let's think about our reference types. So as we drag this across, we always want this to stay in column B. So we're going to fix our column, our lookup array. So where we want to look for this hostID is going to be in our host column. And remember that for a match function you need a one dimensional array, which is why I only selected column A. Again, we're going to fix this since we don't want it to move. And our match type is going to be an exact match, so we can close that comma. And now we need to know which column out of our index array is our host table and where we want to pull the value from there. So this is going to vary, right? Because for some cases we want the response time, for others we want the response rate, then the acceptance rate, and finally the super host.

So we're going to use another match function; we're going to open that up. Our lookup value is going to be the current column that we're on here. So in this case, that's going to be the response time. And again, let's think about our reference types. So as we drag this to the right, we do want to move the response rate, acceptance rate, and super host. But as we apply this down, we don't want a row to move because our headers are always going to be in row 1. So we're going to press F four times to fix our row and leave our column reference relative. So come over to our lookup array. So where do these headers live? Well, they live in the first row of our host table. So we're going to fix that as well, because we don't want this to move down as we apply the formula down. And again, the match type is going to be exact. So zero closes our matchfunction, then final closes parentheses. To close our index function, press Enter, and you'll see that we get there within a few hours.

And what's happening here is this. Our index function is telling Excel, "I want you to return a value from my host table." So Excel is asking, "Okay, so which row is the value in?" And we use a match function to say, "Well, in the row where the host ID is equal to eight, So Excel finds a value of eight, goes to that row, and asks, OK, now which column from this row? So we use the second match function to say, in the column where the table header is equal to response time, So again, Excel finds the value of response time and now knows to return within a few hours. Now let's see what happens when we drag this across. Same results; we just moved the formatting a bit, but we can change that back. And now let's apply this down, and isn't that amazing?

So index and match functions allow us to just simply apply these across and down, and we get the same exact results that we got from VLOOKUP, but without having to adjust our column index number. Now let's do the same for our districts and neighborhoods. So let's delete this. Open our index function. Our array is now going to be in our neighborhoods. So at our neighbourhood table, we can fix that. Our row number, we know, is going to be a match function where the lookup value is our neighbourhood ID. And we want to fix the G column. So four, three times. Our lookup array, which is going to be the A column here in our Neighborhoods table, can fix that as well. And we want an exact match. And our column number is going to another match function where we want to lookup the value of, in this case, our district. and we're going to fix the row. So press F four times, and we want to look for that district here in our headers for our neighborhood table. Fix that. Comment over. We want an exact match. Close this. Close our index function and press Enter. Get Manhattan Health Kitchen and Amazing. So there it is. That's how you pair together the index and match function to make it more powerful and flexible.

26. Approximate Match Lookups

To finish our lookup functions, we'll be reviewing approximate match lookups. So far, we've only been using exact matches, but approximate matches do have their use cases. An approximate match returns the highest value less than or equal to the lookup value if an exact match is not found. So, as an example, consider this table with student IDs and their scores. We want to be able to assign them a letter grade using this reference table. So just by looking at the scores, if we used VLOOKUP with an exact match, we wouldn't get any results since none of the scores here match the scores in the reference table.

And this is where the approximate match comes in; the formula would look like this. So we VLOOKUP where the lookup value is "two," which is our score. The lookup array is E 1 to F 6 since that's where we want to find the score and pull the grades from. And the column index number is two since the grade column is the second one over. Now we don't need to add a return type since an approximate match is the default. So what the formula does is find the highest value in the leftmost column that is less than or equal to 83, which in this case is 80. Then it goes to the second column over, and it returns a value, which in this case is a grade of B. And we could apply this downward, and the formula would use the same logic for the rest. So a score of 51 would be an F, a score of 67 would be a D, and so on and so forth. Now, an important note: for an approximate match to work, the lookup column needs to be sorted in ascending order. So if the minimum score in our reference table wasn't sorted properly, this formula wouldn't have worked, and we'd have gotten crazy results. So now that we know that, let's head over to Excel and use this with our Airbnb data. Here in Excel, we're still in the Places tab of our course project workbook. And you may have noticed that over here to the right we have a small table. And what this table tells us is the service fee that Airbnb charges a host based on the price per night for each place.

So if a place is priced between zero and $199 per night, then Airbnb will charge a 3% service fee. If it's priced between $200 and $499, then it will charge 4% again between $500 and $999, or 5% overall. And if a place costs more than $1,000 per night, then Airbnb will charge a 7% service fee. Now, the price range column is only for visual reference, but really, these are just text strings. So we added the minimum price column, which is like the minimum grade column that we just saw on the example, so we can use it for our approximate match. And what we're going to do is add a column next to our price where we want to include the service fee that each place will be charged based on their price. So we're going to use the VLOOKUP function to open that up. Our lookup value is going to be the price, in this case, $65 or N two. and we're going to look for that price in our table here. So, again, these are just for reference. But what we're actually looking for to be in our leftmost column is the minimum price. And we're going to fix this table with F four. Since this is always going to be here, come over to our column index number. And since our service fees are in the second column over, this will be two. And we could add a range lookup of one, which is an approximate match. But because this is the default value, we can just delete it altogether. Close our formula, press Enter, and you'll see that we get $0 here.

This is because it adopted the formatting from our price column, but we can change this to a percentage. And you'll see that we got 3%. So what's happening here? How did we get this 3%? Well, what Excel is doing is taking our lookup value, which in this case is $65, and looking for an exact match first within the leftmost column of our table array, which is the minimum price. So since it doesn't find the value of 65 here, it's not going to look for the highest value that is less than 65. So in this case, there's only one value less than 65 here, and that's $0. So this is where it's going to land. It's going to go to the second column and bring over the service fee, which is 3%. And as we apply this down, you'll see that here we get 4%, and our price is $239. So again, Excel is going to look for 239 here, and it's not going to find it. So it's going to say, "Okay, what's the next highest value that's less than 239?" Well, zero and 200 are less than 239, but 200 is higher than zero. So then the service fee is 4%. And there you have it, using approximate matches.

27. The PMT & NPER Functions

Now that we've covered lookup functions, it's time to focus on the financial functions covered in the exam. And these functions have a few quirks, so make sure you're paying attention. We'll start with the end-period number of periods function. The N per function returns the number of periods for a loan based on constant payments and a constant interest rate. Syntax wise, it has three required arguments and two optional arguments. The rate is the interest rate for the period, the PMT is the constant payment per period, and since this is an expense, it needs to be a negative number, and the PV is the present value or the loan principle. Now, before I go over the optional arguments, know that these are rarely required as their default values are used most of the time. So the FB is the future value, which defaults to zero, and the type refers to making end-of-period payments, which is the default or beginning of period payments.

So if you encounter a task in the exam that requires you to use this function, don't be alarmed if you can't find a future value or if it doesn't specify if you'll be making a beginning or end of period payment. That just means that you can use the defaults. So again, unless it specifically states otherwise, there's no need to enter anything in these two arguments. As an example, let's say we are purchasing this property. To do so, we had to take out a loan of $300,000 with a 4% annual interest rate, and we'll be making monthly payments. Do you want to know how many months it will take to pay off the loan? But to do so, we can use the N per function. Now, a very important side note before we go over the formula: the time units for the interest rate and the payment period must be the same, and you will always need to subtract the base unit from the payments. So, looking back at our formula, if we will be making monthly payments, as is the case in the example, then we need to make sure that the interest rate provided is converted to a monthly interest rate.

Since we were given an annual interest rate of 4% in cell B-9, this is why we are dividing it by twelve in our formula. Twelve being the twelve months in the year, then moving on to our payment. This is in cell B-10, which is the monthly payment of $1,000, and from the formatting we can tell that the value is already negative. So we're all set. The present value of the loan is insale B eleven, which is 300 grand. And since we were given no other information, we don't need to include anything as a future value or type. Once applied, our formula would give us a result of 331, and you may begin to notice a pattern. Now, since the time unit we used for the interest rate and the payment is monthly, the resulting number of periods is months. The second financial function covered is the PMT, or payment function, which you may have noticed is one of the arguments. In the end per function, the PMT function calculates the periodic payment for a loan based on a fixed period and a constant interest rate. So the syntax is nearly identical, but with the number of periods as the second argument instead of the payment. So again, the rate is the interest rate for the period, and the emperor is the number of periods. And in the PMT function, you also need to make sure that the time units on these match the units for your payment.

So my suggestion is to always look out for keywords like "monthly payments," which are going to tell you which units to use. Moving on, the PV is the present value or loan principle, and we have the same optional arguments. So FB for future value and the type of payment So end of period or beginning of period, and I'm going to keep repeating myself here, but you won't need to use these two unless you're specifically given the information. So similar an example here. But now we want to calculate the monthly payments due with the information given. So our function would look like this: Since we have monthly payments, the rate is B 9 divided by 12. So our annual interest rate of 4% converted to a monthly interest rate, and the N per is going to be B 10, which is the 30 multiplied by twelve. So here, since the term length was given to us in years but a payment period is one month long, we need to convert the 30 years into months. And finally, the present value of the loan is the amount of $300,000 and sells for $11, and we can leave the rest of the arguments blank, which would give us monthly payments of negative $1,432—again, a negative value due to the fact that these are payments. So it's money coming out of our pockets, and that's it.

It may seem like a lot right now because we had to go over the important rules for using these functions. But you'll see as we head over to Excel that, with a little bit of practice, these are actually quite simple here in Excel. I hope you're excited because we're finally going to be working in the mortgage calculator tab of our course project workbook. And what we're looking at here is actually the property that we may want to purchase for Airbnb. So it's this nice little furnished apartment in Chelsea, Manhattan. It's got one bedroom, one bath, and 450 square feet. And these nice little notes about the place that we may want to use for our description on the Airbnb website. But the relevant information is up here. So we have a purchase price of $399 grand.We've got these monthly expenses, which right now add up to $983, although we're still missing the actual monthly payment amount. So we're going to calculate that in a second. And we've got several loan costs, so pretty standard stuff. We have a 20% down payment, an annual interest rate of 5%, and the loan amount comes out to $319,200. And finally, our cash to close the place is $87,780, which includes the down payment and the estimated closing costs. But back to business. We're interested in two scenarios here.

The first is paying for this place for a fixed term of 30 years. And the second is with fixed monthly payments of $1500. So to calculate the monthly payment for a fixed term, we're going to use the PMT function, as you may have guessed. So we're going to write that down. PMT, our rate is going to be our annual interest rate here. And as I say that, notice that we have an annual interest rate and a monthly payment. So we need to make sure our time units are the same. So what we need to do is convert this annual interest rate into a monthly interest rate, and we can simply divide by twelve, then comma over to our number of periods, which is our term length. So that's right here. However, this has been going on for years now. And again, we're using monthly payments, so our time units need to match. So to convert our 30 years into months, we just need to multiply this by twelve, comma over our present value, which is going to be the loan amount. So $319,200 or so, age 15. And in this scenario, we don't need to worry about a present value or a type since the defaults will work just fine.

So we can close that, press Enter, and you'll see that we get a negative amount of $1,714. and this is 100% correct. In fact, if this were the exam, this is the correct answer. But here you'll see that we have expenses as positive numbers. So I'm actually just going to multiply this by a negative one at the end, just to have some coherent information. And you'll see that our monthly expenses go up to $2,696. Now let's look at our fixed payment scenario. So for the term length, we want to calculate the number of periods. So the input function is right, so let's write that down and open that up. Our rate is, again, our annual interest rate. But since we're still working with monthly payments, we'll again divide that by twelve. Our payment is right here. So we sell M4, and our present value is going to be, again, our loan amount. So H 15, close that out, press Enter, and you'll see that we get a negative number, which can't be right. So let's jump back into our function here, press F2, and let's see if we can think about what we did wrong. In fact, you may have already picked it up. Okay, the problem here is that we entered a positive payment value, but these always, always need to be negative. So I'm just going to multiply four here by a negative one. Press Enter, and you'll see that we now get a value of 523 67. And it may seem a bit high now, but again, let's take a moment to think about this. So we have monthly payments. We convert it to a monthly interest rate, which means that our number of periods is six. So this isn't 523 years; it's 523 months. So to convert this to years, we'll just divide the whole thing at the end by twelve. And that's more like it. So our term length, if we were paying monthly amounts of one, say, $500, is going to be 43 years. And we can actually kind of double check this if we change our term length here to 43, and you'll see that we get monthly payments very close to $1500. So little pro tip there. And in our scenario, we were actually only given the fixed term options. So I'm going to go ahead and hide our fixed payments by clicking on this minus button here and hiding those columns. And there we go. The PMT and N per functions It takes a bit of practice, but.

Prepared by Top Experts, the top IT Trainers ensure that when it comes to your IT exam prep and you can count on ExamSnap Microsoft Excel Expert (Excel and Excel 2019) certification video training course that goes in line with the corresponding Microsoft MO-201 exam dumps, study guide, and practice test questions & answers.