Jun 2, 2026

How to Use INDEX MATCH in Excel (with Examples, 2026)

Excel Index Match Spreadsheets How-to Formulas Productivity

INDEX/MATCH is the flexible, future-proof alternative to VLOOKUP. MATCH finds which row a value is in; INDEX returns the value from that row in any column you choose. It can look left, survives inserted columns, and works in every version of Excel.

The Two Functions

=MATCH(lookup_value, lookup_array, 0)   →  returns a row position
=INDEX(return_array, row_number)         →  returns the value at that position

Combine them so MATCH feeds the row number into INDEX.

1. The Core Formula

Column A has product IDs and column C has prices. To get the price for the ID in E2:

=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

MATCH(E2, A2:A100, 0) finds the row where the ID lives (the 0 forces an exact match).
INDEX(C2:C100, …) returns the price from that same row.

Press Enter.

2. Look to the Left

This is the headline advantage over VLOOKUP. If IDs are in column C and the name you want is in column A, INDEX/MATCH doesn’t care about order:

=INDEX(A2:A100, MATCH(E2, C2:C100, 0))

VLOOKUP can’t do this — it only returns columns to the right.

3. Why It Beats VLOOKUP

Looks any direction — return column can be left of the lookup column.
Insert-proof — adding or deleting columns won’t break it, because there’s no hard-coded column number.
Faster on big sheets — it only reads the two columns it needs.
Works everywhere — no Excel 365 requirement (unlike XLOOKUP).

4. Two-Way Lookup (Row and Column)

Use a second MATCH for the column position to look up a value at the intersection of a row and a column header:

=INDEX(B2:E100, MATCH(G1, A2:A100, 0), MATCH(G2, B1:E1, 0))

First MATCH finds the row (e.g., a product in column A).
Second MATCH finds the column (e.g., a month in the header row).
INDEX returns the cell where they meet.

5. Anchor the Ranges

When copying the formula, lock the lookup and return ranges with $ so they don’t drift:

=INDEX($C$2:$C$100, MATCH(E2, $A$2:$A$100, 0))

6. Troubleshooting

#N/A error

MATCH found no exact match. Check for extra spaces (TRIM), text stored as numbers, or a typo. Wrap in IFERROR(…, "Not found") for a friendly message.

#REF! error

INDEX’s row number exceeds the height of the return range, or a referenced cell was deleted. Make sure the return range and the MATCH lookup range are the same height.

Returns the wrong row

Make sure the MATCH lookup array and the INDEX return array start on the same row (both 2:100). If they’re offset, the position won’t line up.

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

Get Carly Today →

Or explore our free tools