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.
In this video, you'll see how to use the MATCH function to:
There are other MATCH examples below the video. For written instructions for this video, see the Contextures Blog article: 30 Excel Functions in 30 Days: 19 - MATCH
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.
The following examples show how to use INDEX and MATCH together in formulas, to find the data that you need
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.
In this example, the MATCH function returns values for 2 of the INDEX arguments:
Here's what the 2 MATCH function do:
The INDEX function, INDEX($B$2:$D$4 returns 30 -- row 3, column 2, in range $B$2:$D$4
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
Here's how the formula works:
The INDEX function,INDEX(Table,, returns 30 -- row 4, column 3, in range named Table.
For some Excel lookups, you might need to match criteria in 2 or more columns. Watch the video, to see the steps, and there are notes below the video.
This is the formula in cell E13, to return the price based on selected Item and Size:
This is an array formula, so press Ctrl+Shift+Enter, after you enter or edit it, instead of just pressing Enter.
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, below. There are notes below the video.
For written steps, go to Find Best Price with Excel INDEX and MATCH on my Contextures blog.
These formulas are shown in the video:
This video shows how to find the distance between cities, using a lookup table and an INDEX / MATCH formula.
To follow along with the video, you can download the City Distance sample file. There are notes below the video.
Here is the formula shown in the video, from cell C3, with city names in A3 and B3:
There are written steps on my Contextures blog.
Your MATCH formula may return an #N/A, even though the value you're looking for appears to be in the lookup array. Here are a few common causes for that error
This video shows how to fix Text vs Number problems, and there are written steps below the video.
A common cause for a MATCH error is that one of the values is a number, and the other is text. For example:
Or, if you have downloaded data from a database:
A) To fix the text/number problem, if possible, convert the text to numbers, using one of the methods shown here:
B) If you cannot convert the data, use one of the following solutions, to convert the lookup value within the MATCH formula
In this example, there is a MATCH formula in cell B9
That formula results in an error, because A9 has text, and the lookup table has numbers (B4:B7).
To fix the problem, type two minus signs (double unary) in the formula, before the lookup value.
That converts a text number to a real number, and it won't have any effect on real numbers.
Here is the formula in cell B10, where A10 is text, and the MATCH function works correctly:
This technique works correctly for lookup values that are numbers too. Here is the formula in cell B11, where A11 is a number:
In this example, there is a MATCH formula in cell G9
That formula results in an error, because F9 has a number, and the lookup table has text (G4:G7).
To fix the problem, add an empty string ("") in the formula, after the lookup value. That converts a real number to a text number, and it won't have any effect on text values.
Here is the formula in cell G10, where F10 is a number, and the MATCH function works correctly:
This technique works correctly for lookup values that are text too. Here is the formula in cell G11, where F11 is text:
If you need to match Excel numbers formatted with leading zeros to text numbers with leading zeros, use the TEXT function in the formula, to convert the lookup value to formatted text.
In this example,
Here is the formula in cell G14, where F14 is a formatted number, and the MATCH function works correctly:
This technique works correctly for lookup values that are text too. Here is the formula in cell G15, where F15 is text:
Another possible cause for MATCH errors is a difference in spaces.
To see if that's the problem, use the LEN function to compare the length of each value.
For example: =LEN(A8) returns the number of characters in cell A8. It should be equal to the number of characters in the matching cell (A5) in the lookup table.
In this example, A8 is one character longer, so the MATCH formula can't find a match, and returns an error.
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.
If you copied data from a web page, it may contain non-breaking space ( ) characters, that look like normal space characters. The MATCH function sees the difference though, and returns an error when you try to match them.
To see if that's the problem, use the CODE function to check the character code for a specific character in the cell.
For example: This formula returns the code for the 8th character in cell A5.
In this example, A8 has a non-breaking space, and A5 has a normal space character, so the MATCH formula can't find a match, and returns an error.
If possible, replace the non-breaking space characters with normal space characters, and the MATCH formula should work correctly.
Or, use the SUBSTITUTE function to replace the non-breaking space, CODE(160), with a normal space character.
Another option is to use a macro to fix the web page characters.
David McRitchie has written a macro to remove them, along with other spaces characters --
Last updated: July 9, 2021 4:05 PM