INDEX MATCH vs VLOOKUP: Which Should You Use?

VLOOKUP is the lookup most people learn first, but INDEX/MATCH and the newer XLOOKUP solve problems VLOOKUP can't. Here's a straight comparison so you can pick the right one.

How INDEX/MATCH works. It's two functions working together. MATCH finds the position of a value in a row or column; INDEX returns the value at that position from another range:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example — find a price (column C) by product code (column A):

=INDEX(C2:C100, MATCH(A2, ProductCodes, 0))

The 0 in MATCH means exact match — the equivalent of VLOOKUP's FALSE.

Where INDEX/MATCH beats VLOOKUP.

It can look left. VLOOKUP can only return a value to the right of the lookup column. INDEX/MATCH points at any two ranges, in any order.
It survives column insertions. VLOOKUP uses a hard-coded column number; insert a column inside the table and the number now points at the wrong place. INDEX/MATCH references the actual return range, so it keeps working.
It can be faster on very large sheets, because it only scans the lookup and return ranges, not the whole table.

Where VLOOKUP still makes sense. It's shorter, more people recognize it, and for a simple left-to-right lookup in a stable table it's perfectly fine. If your team reads each other's spreadsheets and everyone knows VLOOKUP, that familiarity has real value.

The modern answer: XLOOKUP. If your Excel version has it (Microsoft 365 and Excel 2021+), XLOOKUP is the best of both:

=XLOOKUP(A2, ProductCodes, Prices, "Not found")

It looks in any direction, doesn't break on column inserts, has built-in "if not found" handling, and reads more clearly than either alternative. For new spreadsheets on a current version, default to XLOOKUP.

Quick decision guide.

On a current Excel version → XLOOKUP.
On an older version, or need to look left / protect against column changes → INDEX/MATCH.
Simple, stable, everyone-knows-it lookup → VLOOKUP is fine.

One thing they share. All three return #N/A when the value isn't found. Wrap any of them in IFERROR (or use XLOOKUP's fourth argument) to show a friendly message instead.