Excel Index and Match Functions

MATCH Function

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

	A	B
1	Item
2	Sweater
3	Jacket
4	Pants
5	Jacket	=MATCH(A5,ItemList,0)

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.

The result will be like the below figure: 

 
 

INDEX Function

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?

	A	B
1	Item	Price
2	Sweater	10
3	Jacket	30
4	Pants	25
5	=INDEX(A2:B4,3,2)

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.

  You can see the output of the function in below figure.

 

INDEX / MATCH

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.

	A	B
1	Code	Item
2	SW001	Sweater
3	JK002	Jacket
4	PN001	Pants
5
6	Jacket	JK002

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

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

Then, the INDEX function, INDEX(A2:A4,2), returns "JK002", which is the second item in the rangeA2:A4.

INDEX / MATCH -- Example 2

The MATCH function can be used to return values for both the row_num and column_num arguments in the INDEX function.

	A	B	C	D
1		Small	Med	Large
2	Sweater	10	12	15
3	Jacket	30	35	40
4	Pants	25	30	35
5
6	Size	Item	Price
7	Med	Pants	?

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.

The first MATCH function, MATCH(B7,A2:A4,0), returns 3, which is the position of "Pants" in the Items list.

The second MATCH function, MATCH(A7,B1:D1,0), returns 2, which is the position of "Med" in the Size list.

Then, the INDEX function, INDEX(B2:D4,3,2), returns "30", which is the third item in the second column in the range B2:D4.

INDEX / MATCH -- Example 3

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

	A	B	C	D
1		Small	Med	Large
2	Sweater	10	12	15
3	Jacket	30	35	40
4	Pants	25	30	35
5
6	Size	Item	Price
7	Med	Pants	?

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.

The first MATCH function, MATCH(B7,INDEX(Table,,1),0), looks for "Pants" in the first column of the Tablerange (A1:A4), and returns 4.

The second MATCH function, MATCH(A7,INDEX(Table,1,),0), looks for "Med" in the first row of the Tablerange (A1:D1), and returns 3.

Then, the INDEX function, INDEX(Table,4,3), returns "30", which is in the fourth row in the third column in the range named Table

INDEX / MATCH -- Example 4

Instead of matching information in column headings, you may need to match information that's stored in the columns, as shown in the table below.

	A	B	C	D
1	Code	Item	Size	Price
2	SW001	Sweater	Small	10
3	JK001	Jacket	Small	30
4	PN001	Pants	Small	25
5	SW002	Sweater	Med	12
6	JK002	Jacket	Med	35
7	PN002	Pants	Med	30
8	SW003	Sweater	Large	14
9	JK003	Jacket	Large	40
10	PN003	Pants	Large	35
11
12	Item	Size	Price	Code
13	Jacket	Med	?	?

In this INDEX / MATCH example, instead of columns headings of Small, Med, and Large, the size is stored in column C. You need to find the price from column D, when Jacket is in column B, and Med is in column C.

1. Set up the worksheet as shown at right
2. Enter the following formula in cell C13:     
   =INDEX(D2:D10,MATCH(1,(A13=B2:B10)*(B13=C2:C10),0))
3. This is an array formula, so hold Ctrl + Shift, and press the Enter key to see the result.

Curly brackets will be automatically added to the formula (don't type them yourself!), so the final result will look like this:

{=INDEX(D2:D10,MATCH(1,(A13=B2:B10)*(B13=C2:C10),0))}

Next, create a similar INDEX/MATCH formula in cell D13, to get the correct code from column A.

Note: You can adjust the ranges to match the data on your worksheet, but in Excel 2003 and earlier versions you can't refer to an entire column.