Using INDEX and MATCH in Microsoft Excel
While the VLOOKUP function is good for finding values in Excel, it has its limitations. Using a combination of the INDEX and MATCH functions instead, you can look up values in any location or direction in your spreadsheet.
The INDEX function returns a value based on a location you enter into the formula, while MATCH does the reverse and returns a location based on the value you enter. If you combine these functions, you can find any song or text you need.
The difference between these functions and VLOOKUP is that VLOOKUP finds values from left to right. Hence the name of the function; VLOOKUP performs a vertical search.
Microsoft explains the best the way VLOOKUP works†
There are certain limitations when using VLOOKUP: The VLOOKUP function can only look up a value from left to right. This means that the column with the value you are looking for must always be to the left of the column with the returned value.
Microsoft goes on to say that if your sheet isn’t set up in a way that VLOOKUP can help you find what you need, you can use INDEX and MATCH instead. So let’s take a look at how to use INDEX and MATCH in Excel.
Basics of INDEX and MATCH Functions
To use these functions together, it is important to understand their purpose and structure.
The syntax for INDEX in array is INDEX(array, row number, column number) where the first two arguments are required and the third is optional.
INDEX looks up a position and returns the value. To find the value in the fourth row in the cell range D2 to D8, enter the following formula:
The result is 20,745, because that’s the value in the fourth position of our cell range.
For more details on INDEX’s array and reference forms and other ways to use this feature, check out our how-to for INDEX in Excel.
The syntax for MATCH is MATCH(value, array, match_type) with the first two arguments required and the third optional.
MATCH looks up a value and returns its position. To find the value in cell G2 in the range A2 to A8, enter the following formula:
The result is 4 because the value in cell G2 is in the fourth position in our range of cells.
Check out our tutorial for MATCH in Excel to learn more about the match_type argument and other ways to use this function.
RELATED: How to Find the Position of a Value Using MATCH in Microsoft Excel
Using INDEX and MATCH in Excel
Now that you know what each function does and what its syntax is, it’s time to put this dynamic duo to work. Below we use the same data as above for INDEX and MATCH separately.
You put the formula for the MATCH function in the formula of the INDEX function instead of the position to look up.
To find the value (sales) based on the location ID, use this formula:
The result is 20,745. MATCH finds the value in cell G2 within the range A2 through A8 and passes it to INDEX, which looks to cells D2 through D8 for the result.
Let’s look at another example. We want to know which city has sales that correspond to a certain amount. Using our sheet, you would enter this formula:
The result is Houston. MATCH finds the value in cell G5 in the range D2 through D8 and passes it to INDEX, which looks to cells B2 through B8 for the result.
Here is an example where an actual value is used instead of a cell reference. We find the value (sales) for a specific city using this formula:
In the MATCH formula, we replaced the cell reference containing the lookup value with the actual lookup value of “Houston” from B2 to B8, which gives us the result 20,745 from D2 to D8.
Note: When using the actual value to look up, rather than a cell reference, make sure to enclose it in quotes, as shown here.
To get the same result by using the location ID instead of the city, we simply change the formula to this:
Here we modified the MATCH formula to look up “2B” in the cell range A2 through A8 and provide that result to INDEX, which then returns 20,745.
Basic functions in Excel, such as those that allow you to add numbers in cells or enter the current date, are certainly useful. But when you start adding more data and improving your data entry or analysis needs, lookup functions like INDEX and MATCH in Excel can be quite useful.
RELATED: 12 Basic Excel Functions Everyone Should Know