Excel Index and Match Functions

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

INDEX and MATCH Introduction

• The INDEX function can return an item from a specific position in a list.
• The MATCH function can return the position of a value in a list.
• The INDEX / MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.

Video: INDEX Function

In this video, you'll see how to use the INDEX function to:

• Find sales amount for selected month
• Get reference to specified row, column, area
• Create a dynamic range based on count
• Sort column of text in alphabetical order

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

INDEX Function Arguments

The INDEX function has three arguments:

1. array: Where is the list? If you use an absolute reference ($A$2:$B$4), instead of a relative reference (A2:B4), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
2. row_num: Which row has the value you want returned? In this example, the item in the third row will be returned.
3. [column_num]: Which column has the value you want returned? In this example, the item in the second column will be returned.

INDEX Function Example

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?

1. Select the cell in which you want the result
2. Type an equal sign, the INDEX function name, and an opening parenthesis:
         =INDEX(
3. Select the cells that contain the list -- cells A2:B4 in this example
4. Press the F4 key on the keyboard, to change the reference to an absolute reference: $A$2:$B$4. Then, if you copy the formula to another cell, it will continue to refer to the list of items.
5. Type a comma, to separate the arguments
6. Type the number of the item you want to return -- 3 in this example
7. Type a comma, to separate the arguments
8. Type the number of the column you want to return -- 2 in this example
9. Finish with a closing parenthesis. The completed formula is:
         =INDEX($A$2:$B$4,3,2)
10. Press the Enter key to see the result. 

Interactive INDEX Example

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.

Video: MATCH Examples

In this video, you'll see how to use the MATCH function to:

• Find position of item in unsorted list
• Use with CHOOSE to get student grades
• Use with VLOOKUP for flexible column choice
• Use with INDEX for to show winner's name

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 Arguments

The MATCH function has three arguments:

To view the steps in a short video, click here

1. lookup_value: What value do you want to find in the list? You can type the value, or refer to a cell that contains the value.
2. lookup_array: Where is the list?
3. [match_type]: Match_type can be -1, 0, or 1. It tells Excel how to match the lookup_value to values in the lookup_array.
    1 -- find the largest value less than or equal to lookup_value
            (the list must be in ascending order)
    0 -- find the first value exactly equal to lookup_value. Lookup_array
            (the list can be in any order)
   -1 -- find the smallest value greater than or equal to lookup_value.
            (the list must be in descending order)
   Note: If match_type is omitted, it is assumed to be 1.

MATCH Function Example

The MATCH function can find a value in a list, and return its position. For example, where is "Jacket" in the list below?

1. Select the cell in which you want the result
2. Type an equal sign, the MATCH function name, and an opening parenthesis:
         =MATCH(
3. Type the value to find, or click on the cell that contains the value -- cell A5 in this example
4. Type a comma, to separate the arguments
5. Select the range that contains the list -- a range named ItemList in this example
6. Type a comma, to separate the arguments
7. Type the number for the Match type you want to use -- 0 was used in this example, because an exact match is required.
8. Finish with a closing parenthesis. The completed formula is:
         =MATCH(A5,ItemList,0)
9. Press the Enter key to see the result.

INDEX and MATCH - Examples

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

1) Find Code - Product

2) Find Price - Product & Size

3) Find Price - Product - Flexible

4) Find Price - 2 Criteria Columns

Ex 1 - Find Code - Product

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.

1. Set up the worksheet as shown at right
2. Enter the following formula in cell B6:
         =INDEX($A$2:$A$4,MATCH(A6,$B$2:$B$4,0))
3. Press the Enter key to see the result.

The MATCH function, MATCH(A6,$B$2:$B$4,0), returns 2, which is the position of "Jacket" in the list.

Then, the INDEX function, INDEX($A$2:$A$4,2), returns "JK002", which is the second item in the range $A$2:$A$4. 

Ex 2 - Find Price - Product & Size

In this example, the MATCH function returns values for 2 of the INDEX arguments:

• row_num - Find the Item name in column A
• column_num - Find the Size in row 1

1. Set up the worksheet as shown above
2. Enter the following formula in cell C7:     
• =INDEX($B$2:$D$4, MATCH(B7,$A$2:$A$4,0), MATCH(A7,$B$1:$D$1,0))
3. Press the Enter key to see the result.

Here's what the 2 MATCH function do:

1. MATCH(B7,$A$2:$A$4,0) - returns 3 - row number in the range A2:A4, where the item "Pants" is found
2. MATCH(A7,$B$1:$D$1,0) - returns 2 - column number in the range B1:D1, where the size "Med" is found

The INDEX function, INDEX($B$2:$D$4 returns 30 -- row 3, column 2, in range $B$2:$D$4

Ex 3 - Find Price - Flexible

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

1. The green cells are a range named Table). (Instructions on naming a range)
2. Enter the following formula in cell C7:     
• =INDEX(Table, MATCH(B7,INDEX(Table,,1),0), MATCH(A7,INDEX(Table,1,),0))
3. Press the Enter key to see the result.

Here's how the formula works:

• MATCH(B7,INDEX(Table,,1),0) - returns 4 - row number in Table range, where "Pants" is found in column 1
• MATCH(A7,INDEX(Table,1,),0) - returns 3 - column number in Table range, where "Med" is found in row 1

The INDEX function,INDEX(Table,, returns 30 -- row 4, column 3, in range named Table.

Ex 4 - Find Price - 2 Criteria Columns

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.

Price Lookup -2 Criteria Columns

Here 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:

• =INDEX(E2:E10, MATCH(1, (C13=$C$2:$C$10) * (D13=$D$2:$D$10),0))

This is an array formula, so press Ctrl+Shift+Enter, after you enter or edit it, instead of just pressing Enter.

• NOTE: In Excel 365, you can just press Enter.

Find Best Price - INDEX, MATCH, MIN

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:

• Cell E2 - Best Price in each row: =MIN(B2:D2)
• Cell F2 - Store with best price: =INDEX(B$1:D$1,, MATCH(E2,B2:D2,0))

Distance Between Cities - INDEX / MATCH

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:

• =INDEX(G3:P12, MATCH(A3,F3:F12,0), MATCH(B3,G2:P2,0))

There are written steps on my Contextures blog.

Troubleshoot the MATCH formula

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

-- Text vs Number

-- Space Characters

-- HTML Characters

This video shows how to fix Text vs Number problems, and there are written steps below the video.

Text vs. Number

A common cause for a MATCH error is that one of the values is a number, and the other is text. For example:

• the lookup range might have '123 (text)
• the value to look up is 123 (a number)

Or, if you have downloaded data from a database:

• downloaded data may have text codes with leading zeros, e.g. 00123
• your Excel file has numbers formatted with leading zeros

Fix the Text/Number Problem

A) To fix the text/number problem, if possible, convert the text to numbers, using one of the methods shown here:

• Convert Text to Numbers

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 Numbers

In this example, there is a MATCH formula in cell B9

• =MATCH(A9,$B$4:$B$7,0)

That formula results in an error, because A9 has text, and the lookup table has numbers (B4:B7).

Change the MATCH Formula

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:

• =MATCH(--A10,$B$4:$B$7,0)

This technique works correctly for lookup values that are numbers too. Here is the formula in cell B11, where A11 is a number:

• =MATCH(--A11,$B$4:$B$7,0)

2) Lookup values are Numbers, table contains Text

In this example, there is a MATCH formula in cell G9

• =MATCH(F9,$G$4:$G$7,0)

That formula results in an error, because F9 has a number, and the lookup table has text (G4:G7).

Change the MATCH Formula - Empty String

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:

• =MATCH(F10 & "",$G$4:$G$7,0)

This technique works correctly for lookup values that are text too. Here is the formula in cell G11, where F11 is text:

• =MATCH(F11 & "",$G$4:$G$7,0)

Change the MATCH Formula - TEXT Function

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,

• cells F13:F14 are real numbers, formatted to show leading zeros - "00000".
• cell F15 is a text number - '00125

Here is the formula in cell G14, where F14 is a formatted number, and the MATCH function works correctly:

• =MATCH(TEXT(F14,"00000"),$G$4:$G$7,0)

This technique works correctly for lookup values that are text too. Here is the formula in cell G15, where F15 is text:

• =MATCH(TEXT(F15,"00000"),$G$4:$G$7,0)

Spaces in one value, not in other

Another possible cause for MATCH errors is a difference in spaces.

• One of the values might have leading spaces (or trailing, or embedded spaces)
• The other value doesn't have those space characters

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.

• =MATCH(A8,$A$4:$A$6,0)

Fix the Space Problem

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.

• =MATCH(TRIM(A8),$A$4:$A$6,0)

HTML characters in one value, and not the other

If you copied data from a web page, it may contain non-breaking space (&nbsp) 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.

• ="Char #8 = " & CODE(MID(A5,8,1))

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.

• =MATCH(A8,$A$4:$A$6,0)

Fix the Non-Breaking Space Problem

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.

• =MATCH(SUBSTITUTE(A8,CHAR(160)," "),$A$4:$A$6,0)

Use a Macro to Fix Characters

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 --   

Get Sample Files

1. INDEX/MATCH Examples: Get the sample INDEX/MATCH workbook to see the INDEX examples, MATCH examples, INDEX/MATCH examples 1-4, and MATCH troubleshooting examples. The workbook is in xlsx format, and does not contain any macros
2. Best Price: To follow along with the Best Price video, download the Best Price workbook. It is in xlsx format, and zipped. There are no macros in the file.
3. City Distance: To follow along with the Distance Between Cities video, download the City Distance sample file. The zipped file is in xlsx format, and there are no macros in the file.

More Tutorials

Lookup - 2 Criteria

Compare Lookup Functions

VLOOKUP

COLUMNS Function

TEXT function