Use the INDEX and MATCH functions to pull data from a list. These functions can be used together, to create a powerful and flexible formula
In this video, you'll see how to use the INDEX function to:
There are other INDEX examples below the video. For written instructions for this video, see the Contextures Blog article: 30 Excel Functions in 30 Days: 24 - INDEX
The INDEX function has three arguments:
The INDEX function can return an item from a specific position in a specific column in a list. For example, what is the 3rd item in the 2nd column in the list below?
In the embedded Excel file shown below, you can see the INDEX function example. On Sheet 1, the row and column numbers are typed in the formula -- hard-coded.
NOTE: The interactive file might not be viewable on all devices or browsers.
On Sheet 2, the row and column numbers are in worksheet cells. Change the row or column number to change the formula result.
The MATCH function has three arguments:
To view the steps in a short video, click here
The MATCH function can find a value in a list, and return its position. For example, where is "Jacket" in the list below?
One advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.
In this INDEX / MATCH example, the MATCH function will find the position of "Jacket" in column B, and the INDEX function will return the code from the same position in column A.
The MATCH function, MATCH(A6,$B$2:$B$4,0), returns 2, which is the position of "Jacket" in the list.
The MATCH function can be used to return values for both the row_num and column_num arguments in the INDEX function.
The first MATCH function, MATCH(B7,$A$2:$A$4,0), returns 3, which is the position of "Pants" in the Items list.
The second MATCH function, MATCH(A7,$B$1:$D$1,0), returns 2, which is the position of "Med" in the Size list.
To make the previous example even more flexible, you can use the INDEX function within the MATCH function, to look for values in the first row or column of a named table. (This INDEX / MATCH example is from a newsgroup posting by Peo Sjoblom)
Set up the worksheet as shown above
The first MATCH function, MATCH(B7,INDEX(Table,,1),0), looks for "Pants" in the first column of the Table range (A1:A4), and returns 4.
The second MATCH function, MATCH(A7,INDEX(Table,1,),0), looks for "Med" in the first row of the Table range (A1:D1), and returns 3.
For some Excel lookups, you may need to match 2 or more criteria. This video shows how to use 2 criteria - Item name and product Size, to get the correct price.
For details on how this formula works, to to the Lookup - 2 Criteria page.
This video shows how to combine INDEX, MATCH and MIN, to show the name of the store that has the lowest price. MIN calculates the lowest price, and MATCH locates that price in the row. INDEX returns the store name for the selected column.
To follow along with this video, download the Best Price workbook. It is in xlsx format, and zipped. There are no macros in the file.
For written instructions, go to Find Best Price with Excel INDEX and MATCH on my blog.
From a lookup table with distances between cities, you can use the INDEX and MATCH functions to show the mileage between two selected cities.
Watch this video to see the steps, and you can download the video's sample file to see how it works.
Your MATCH formula may return an #N/A, even though the value you're looking for appears to be in the lookup array.
A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup array may contain '123 (text), and the value to look up is 123 (a number). Or, if you have downloaded data from a database, it may contain text codes with leading zeros, e.g. 00123, and your Excel file may contain numbers formatted with leading zeros.
If possible, convert the text to numbers, using one of the methods
Convert Text to Numbers
If you can't convert the data, you can convert the lookup value within the MATCH formula:
If the lookup array contains numbers, and the value to look up is text, use a formula similar to the following:
The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.
If the lookup array contains text, and the value to look up is numeric, use a formula similar to the following:
=MATCH(A5 & ""),ItemList,0)
The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.
Another potential cause for no matching value being found is a difference
in spaces. One of the values may contain leading spaces (or trailing,
or embedded spaces), and the other doesn't. To test the values, you
can use the LEN function, to check the length of each value.
For example: =LEN(A5) will return the number of characters in cell A5. It should be equal to the number of characters in the matching cell in the lookup table.
If possible, remove the unnecessary spaces, and the MATCH formula should
work correctly. If you can't remove the spaces, use the TRIM function
in the MATCH, to remove leading, trailing or duplicate spaces. For example:
If you copied data from a web page, it may contain non-breaking space ( ) characters. David McRitchie has written a macro to remove them, along with other spaces characters -- https://www.mvps.org/dmcritchie/excel/join.htm#trimall
Download the sample INDEX/MATCH workbook
Last updated: October 10, 2020 2:15 PM