XLOOKUP vs VLOOKUP: why XLOOKUP wins

If you still reach for VLOOKUP, this is the upgrade. XLOOKUP does everything VLOOKUP does and fixes the four things that quietly break VLOOKUP-heavy workbooks.

The syntax

=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")

Example — pull a location name from an ID table:

=XLOOKUP(A2, IDs, Locations, "Unknown ID")

A2 is what you're looking up, IDs is the column to search, Locations is the column to return from, and "Unknown ID" is what shows instead of an error when there's no match.

Why it beats VLOOKUP

It looks left or right. VLOOKUP can only return a column to the right of the key. XLOOKUP doesn't care about column order.
Exact match is the default. VLOOKUP defaults to approximate match — the source of countless silent wrong answers. XLOOKUP matches exactly unless you tell it otherwise.
Clean "not found" message. The fourth argument replaces #N/A with whatever you want — no wrapping in IFERROR just to hide an error.
It survives inserted columns. VLOOKUP's hard-coded column number (,3,) breaks the moment someone inserts a column. XLOOKUP points at real ranges, so it keeps working.

On older Excel? Use INDEX/MATCH

XLOOKUP needs Microsoft 365 or Excel 2021+. On anything older, INDEX/MATCH does the same job and is just as robust:

=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))

The 0 forces an exact match — don't leave it off.

One gotcha

Lookups fail silently when the two sides look identical but aren't — usually a trailing space or a number stored as text. If a match you expect returns "Not found," wrap both sides in TRIM/CLEAN and confirm the data types line up before you trust the result.

Matching payments to deposits is a lookup problem at heart — and when it spans several entities every month, TrueBalance runs the three-pass match for you. See the manual version in how to compare two lists in Excel.