INDEX and MATCH is a lookup technique that works in every version of Excel. It can look left, it doesn’t break when you insert columns, and it can do two-way lookups. Before XLOOKUP existed, it was the professional’s choice.
The trick is to understand each function on its own first. Then combining them is easy.
Step 1: What INDEX does
INDEX returns the value at a given position in a range.
=INDEX(range, row_number)Using this list in B2:B6:
| Row | Code (A) | Product (B) | Price (C) |
|---|---|---|---|
| 1 | P100 | Keyboard | 15 |
| 2 | P101 | Mouse | 8 |
| 3 | P102 | Monitor | 120 |
| 4 | P103 | USB Cable | 3 |
| 5 | P104 | Headset | 25 |
=INDEX(B2:B6, 3)returns Monitor, the 3rd item in the range.
Step 2: What MATCH does
MATCH does the opposite. It finds a value and tells you its position.
=MATCH(lookup_value, range, 0)=MATCH("P102", A2:A6, 0)returns 3, because P102 is the 3rd item. The 0 means exact match. Always include it.
Step 3: Put them together
MATCH finds the row number, and INDEX uses it to return the value:
=INDEX(C2:C6, MATCH(E2, A2:A6, 0))Read it as: “give me the price from C2:C6, at the position where E2 appears in A2:A6.” If E2 is P102, the result is 120.
Looking to the left
This is where INDEX/MATCH beats VLOOKUP. To find a product’s code from its name, just swap the ranges:
=INDEX(A2:A6, MATCH(E2, B2:B6, 0))If E2 is “Mouse”, the result is P101. VLOOKUP can’t do this because the code column is to the left of the name.
Two-way lookup (row and column)
INDEX can also take a column number. Combine it with two MATCH functions to look up a value by both row and column, like sales for a specific region and month.
| Region | Jan | Feb | Mar |
|---|---|---|---|
| North | 1,200 | 1,350 | 1,500 |
| South | 900 | 1,100 | 1,050 |
| East | 1,400 | 1,250 | 1,600 |
With the table in A1:D4, region in G2 and month in H2:
=INDEX(B2:D4, MATCH(G2, A2:A4, 0), MATCH(H2, B1:D1, 0))For “South” and “Feb”, the result is 1,100.
Lock your ranges
Before copying the formula down, press F4 to add $ signs to the ranges:
=INDEX($C$2:$C$6, MATCH(E2, $A$2:$A$6, 0))Handling missing values
If the value isn’t found, MATCH returns #N/A. Wrap the formula in IFNA:
=IFNA(INDEX($C$2:$C$6, MATCH(E2, $A$2:$A$6, 0)), "Not found")Common mistakes
- Forgetting the 0 in MATCH. Without it, MATCH uses approximate match and may return the wrong row.
- Ranges of different sizes. The INDEX range and MATCH range must start on the same row and have the same number of rows.
- Extra spaces or text numbers. These cause #N/A, just like VLOOKUP. See how to convert numbers stored as text.
INDEX/MATCH vs VLOOKUP vs XLOOKUP
| VLOOKUP | INDEX/MATCH | XLOOKUP | |
|---|---|---|---|
| Look left | No | Yes | Yes |
| Safe when inserting columns | No | Yes | Yes |
| Two-way lookup | No | Yes | Yes (nested) |
| Works in all Excel versions | Yes | Yes | No (365/2021+) |
| Easy to read | Yes | Medium | Yes |
If you have Excel 365, XLOOKUP is simpler. If you need your file to work in older versions, INDEX/MATCH gives you the same power. Compare all three in XLOOKUP vs VLOOKUP.