Home > Formulas > Lookup > INDEX MATCH Excel Index and Match FunctionsUse the INDEX and MATCH functions to pull data from a list. These functions can be used together, to create a powerful and flexible formula Note: For flexible lookup formulas in Microsoft Excel 365, try the new XLOOKUP function. |
Video: MATCH ExamplesIn this video, you'll see how to use 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 |
MATCH Function ArgumentsThe Excel MATCH function syntax has three arguments: To view the steps in a short video, click here
|
INDEX and MATCH - ExamplesOne 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 MATCH together in formulas, to find the data that you need 2) Find Price - Product & Size |
Ex 3 - Find Price - FlexibleTo 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 two-way lookup 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. |
Ex 4 - Find Price - 2 Criteria ColumnsFor some Excel lookups, you might need to match criteria in 2 or more columns. Watch the video, to see example Excel formulas, and there are notes below the video. Price Lookup -2 Criteria ColumnsHere is the lookup table from the video, and the the written steps are on the Lookup - 2 Criteria page. If you download the INDEX/MATCH sample file below, this is on the Ex 4 worksheet. 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.
|
Find Best Price - INDEX, MATCH, MINThis 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:
|
Distance Between Cities - INDEX / MATCHThis video shows how to find the distance between cities, using a lookup table and use 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. |
Find Text from Code List - INDEX / MATCHIn this example, the requirement is to find specific codes within a cell's text string:
Here is the formula in cell B1, and it returns "CAT", which is one of the codes in column D:
Note: The formula is array-entered, with Ctrl+Shift+Enter (not required in Excel 365) There are wildcard characters before and after the cell references to D1:D3, so the text will be found anywhere within the text string in cell A1. There are more details on my Contextures blog, and the Find Text From Code List sample file is in the download section, below. |
Text vs. NumberA 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:
Fix the Text/Number ProblemA) 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 -- 1) Lookup Text, Table Numbers -- 2) Lookup Numbers, Table Text 1) Lookup values are Text, table contains NumbersIn 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). Change the MATCH FormulaTo 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:
|
2) Lookup values are Numbers, table contains TextIn 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). Change the MATCH Formula - Empty StringTo 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:
|
Change the MATCH Formula - TEXT FunctionIf 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:
|
Spaces in one value, not in otherAnother 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.
Fix the Space ProblemIf 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.
|
HTML characters in one value, and not the otherIf 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.
Fix the Non-Breaking Space ProblemIf 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, CHAR(160), with a normal space character.
Use a Macro to Fix CharactersAnother 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 -- |
Video: INDEX Function - 4 ExamplesIn this video tutorial, you'll see 4 examples of how to use the INDEX function, from easy to advanced level. Video Timeline:
There are other INDEX examples above the video. For written instructions for this video, see the Contextures Blog article: 30 Excel Functions in 30 Days: 24 - INDEX |
Get Sample Files
|
More Tutorials |
Last updated: August 12, 2023 3:24 PM