XLOOKUP vs VLOOKUP: choosing the right lookup

VLOOKUP searches the first column of a range and returns a field by position. XLOOKUP searches any column and returns a matching column or block by reference. That difference in contract explains most of the practical choice between them, but the defaults decide whether a mistake is visible.

VLOOKUP defaults to approximate matching when the fourth argument is omitted, so an unsorted table can return a wrong but plausible result. XLOOKUP defaults to exact matching, can look to the left, and accepts a fallback value when no match exists. One function asks you to count columns; the other asks you to name the field you want.

The table compares the requirements that decide most real workbooks. A weakness in one row rarely matters on its own; two or three together usually settle the choice.

The older function is not obsolete. It is embedded in more workbooks than any replacement, and a shared file that must open anywhere still benefits from that reach. The question is whether compatibility is worth the maintenance cost of column positions and approximate-match defaults.

Use XLOOKUP when the workbook stays inside an environment that supports it, the return field may move, or a missing key needs a deliberate fallback. It removes the column-counting step that causes many reporting errors, and the return range makes the intent readable without a helper column.

Use VLOOKUP when compatibility is a hard requirement, or when a simple left-to-right exact lookup on a small, stable table is all that is needed. Use INDEX and MATCH when you need a portable alternative that works across older builds and handles leftward lookups. None of the three is a performance problem at ordinary reporting sizes.

A lookup is only as reliable as its key. Remove leading and trailing spaces, keep numbers as numbers, and check for duplicates before trusting the result. A text value that looks like a number will not match a numeric key, and a trailing space produces the same #N/A as a genuinely missing record.

Point the lookup at a table column, not a whole column, and keep the key column free of blanks and merged cells. If several records share a key, a lookup returns the first match it finds, which may not be the one you meant. Aggregate with SUMIFS or filter the candidates explicitly.

A typical exact lookup reads: =XLOOKUP([@Customer], Customers[Customer], Customers[Rate], NA()). The NA() fallback keeps a missing customer visible instead of returning a plausible zero. The INDEX and MATCH equivalent is =INDEX(Customers[Rate], MATCH([@Customer], Customers[Customer], 0)).

Build a small test block with a normal key, a missing key, a duplicate key, a number stored as text, and a blank. Run both the old and new formulas against it and compare. A migration that only tests the happy path has not tested the lookup.

Replace one column at a time, keep the old result visible until the totals reconcile, then remove the helper. Document which function the workbook expects and why, because a later editor may reintroduce VLOOKUP without knowing the compatibility constraint.

Keep a copy of the old formula column until the next reporting cycle. If a total moves, compare the old and new results row by row before deciding which is wrong. The comparison is quicker than rebuilding trust in a migrated workbook from memory.