Now that you’ve mastered Vlookups (here) – and you probably got the job (woohoo!) – I thought it might be a good time to introduce Vlookup’s more advanced, powerful, and flexible brother: INDEX/MATCH.
While Vlookup is more popular, INDEX/MATCH is kind of like Vlookup’s older, wiser brother who’s in college – maybe he’s not as popular but he’s way more powerful.
In this post I’ll go over how to use INDEX/MATCH and a really cool application. (The example is similar to one of the first spreadsheets I created. It gets a lot of “oohs and ahs”. It’s pretty magical (but easy to create!)).
Soooo .. what is it?
INDEX and MATCH are two separate functions in Excel:
- What it does : MATCH searches for a specified item in a range of cells and returns the relative position in that range.
- The format : MATCH(n,A1:A3,0) .
- n = what value do we want MATCH to find?
- A1:A3 = where do we want MATCH to search for this value?
- 0, 1, or -1 = 0 is used for exact match.
- Example : If the range A1:A3 contains the values: krispy, bacon, and munchkin. Then the formula =MATCH(“munchkin”,A1:A3,0) would return the value 3 because munchkin is the 3rd value in the range.
- What it does : INDEX returns the value in an array when the user specifies the row and column indexes.
- The format : INDEX(array, row_num, column_num)
- array = this is where we want INDEX to search.
- row_num = this is what row within the array the value we are searching for is located in.
- column_num = this is the column within the array the value we’re searching for is in.
- Example: If the array A1:D5 contains the following values:
Our formula =INDEX(A1:D5, 3,4) would return the value 48 because the value in the 3rd row and 4th column of the array is 48. Makes sense, right?
Combining INDEX and MATCH :
The format will be : =INDEX(array, MATCH(n, array, 0), column_num) .
Essentially, the MATCH function is replacing the row_num field.
INDEX/MATCH Formula Example (the magic!):
Let’s say we want to build a spreadsheet in which the ultimate goal is to create a tool where all the user has to do is type in the store’s username (the yellow field) and magically, the store specific data will populate in the row below.
We have these two spreadsheets set up.
We’ll type the following FORMULA in Sheet 1, Cell A4:
- Sheet2!A1:E9 – this is where we’re looking for the value.
- MATCH(Sheet1!$B$1,Sheet1!$A$1:$A$9,0) – this formula will search for B1 from Sheet1 in Column A of Sheet2 and return the row number where it’s located, which then tells INDEX what row we’re looking in.
- 1 – this is the column we’re looking at.
*Important Note : We want both Sheet1B1 and the array in the MATCH formula to be an absolute reference that way when we drag the formula to fill the columns to the right it won’t change.
Now, once we drag that formula out to the last column on the right we’ll have the entire row of data for that username available to the user of the spreadsheet just by typing the username. Pretty cool, right?!
Just for fun – Here’s a video of how to create the formula and then use the spreadsheet!
Now, I bet you’re wondering when exactly you would use a spreadsheet like this. When I first created it, the reason I created it was because we had just billed thousands of customers a new type of bill which was based on their usage. A lot of customers e-mailed/ called requesting the data specific to their practice. With a very high demand for these reports and not enough people to create them, I created the spreadhsheet so all we would have to do is enter their username and we’d have a spreadsheet to send each customer within a few seconds. As you can imagine it made life considerably easier! That is just one application, I’m sure there are many more I haven’t even thought of! I hope this helps someone!