INDEX / MATCH Function in MS Excel Example

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.

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.

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