=INDEX(C2:C6,MATCH(F2,A2:A6,0)) finds the row where F2 appears in A2:A6 and returns the value from the same row of C2:C6. MATCH finds the position, INDEX fetches the value at that position. It works in every version of Excel, and it can look to the left.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
Change F2 to Milk and G2 returns $1.10. Change C2:C6 to B2:B6 and it returns the category instead.
How INDEX and MATCH work together
The formula is two steps in one cell. Here they are in separate cells, so you can see what each part returns.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
MATCH(G1,A2:A6,0) returns 4, because Bread is the fourth item of A2:A6. INDEX(C2:C6,4) returns the fourth item of C2:C6, $2.40. Put the MATCH inside the INDEX in place of G2 and you have the one-cell formula. Two rules make it work:
- The two ranges must start on the same row and have the same height.
MATCH(...,A2:A6,0)counts from row 2, so INDEX must readC2:C6, notC1:C6(which would return the row above). - End MATCH with 0. Without it MATCH does an approximate match that assumes column A is sorted, and on a list of names it can return the position of the wrong row. The MATCH page covers its three match types.
If the value is not in the list, MATCH returns #N/A and so does the whole formula. =IFNA(INDEX(C2:C6,MATCH(F2,A2:A6,0)),"Not found") shows text instead.
Lookup to the left
VLOOKUP returns columns to the right of the one it searches. INDEX and MATCH do not care about the order: search column D, return column A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
P-310 returns Bread. Type P-205 in F2 for Carrot. With VLOOKUP you would have to move the Code column to the front of the table first.
Two-way lookup: INDEX with two MATCHes
INDEX takes a row number and a column number. Give it a whole table and let one MATCH find the row and another find the column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
East is row 3 of A2:A5 and Mar is column 3 of B1:D1, so INDEX returns row 3, column 3 of B2:D5: 5,600. The row MATCH searches down the first column, the column MATCH searches across the header row, and both ranges line up with the table B2:D5.
Why INDEX MATCH beats VLOOKUP
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
Both return the price. The difference shows up when the sheet changes:
- Inserting a column. Insert a column between Category and Price, and the VLOOKUP still asks for column 3, which is now the new empty column. Excel adjusts
C2:C6in the INDEX version toD2:D6and it keeps working. - Looking left. Shown above: VLOOKUP cannot, INDEX MATCH can.
In Excel 2021 and Microsoft 365, XLOOKUP does both in one function with simpler arguments. INDEX MATCH is still the choice for files that have to open in Excel 2019 or older, and the INDEX part is useful on its own. For lookups on two conditions at once, see lookup with multiple criteria.
Practice: look to the left
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
Your turn: The product names are in the last column. In G2, return the price of the product in F2 with INDEX and MATCH.
Practice: a two-way lookup
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
Your turn: In B9, return the score of the student in B7 for the subject in B8.
Frequently Asked Questions
How does INDEX MATCH work?
MATCH finds the position of a value in a column, and INDEX returns the value at that position in another column. In =INDEX(C2:C6,MATCH("Pear",A2:A6,0)), MATCH returns 2 because Pear is the second item of A2:A6, and INDEX returns the second item of C2:C6.
Why use INDEX MATCH instead of VLOOKUP?
It can return a column to the left of the one it searches, and it does not break when a column is inserted inside the table (there is no column number to go stale). In Excel 2021 and Microsoft 365, XLOOKUP gives the same advantages in one function.
What does the 0 in MATCH mean?
It asks for an exact match. Without it MATCH uses match_type 1, an approximate match that expects the column to be sorted ascending, and on an unsorted list it can return the position of the wrong row.
How do I do a two-way lookup with INDEX MATCH?
Give INDEX a whole table and two MATCHes, one for the row and one for the column: =INDEX(B2:D5,MATCH("South",A2:A5,0),MATCH("Feb",B1:D1,0)) returns the value where the South row meets the Feb column.
