XLOOKUP vs VLOOKUP

The short answer: use XLOOKUP for anything new. It searches in either direction, defaults to exact match, handles “not found” without a wrapper, and — the one that actually costs people money — it does not silently break when someone inserts a column.

Keep VLOOKUP for one reason only: the file has to open in Excel 2019 or earlier, where XLOOKUP does not exist.

The difference that causes real damage

VLOOKUP finds its answer by counting columns:

=VLOOKUP(102, A2:D4, 3, FALSE)

That 3 means “the third column of the range.” It is a position, not a reference to anything. Insert a column anywhere inside A:D and the third column is now a different column — the formula keeps working, keeps returning a number, and the number is wrong. Nothing errors. Nobody notices.

XLOOKUP takes two independent ranges instead:

=XLOOKUP(102, A2:A4, C2:C4)

Search this range, return from that range. Insert a column between them and both references move with the data, because they are references rather than a count. This one change is most of the argument.

It can look in either direction

VLOOKUP can only return a column to the right of the one it searches. That is not a limitation of how you write it — it is structural, because the lookup column must be the leftmost column of the range.

XLOOKUP has no such rule. Searching a column and returning one to its left is ordinary:

This is why so many spreadsheets contain INDEX/MATCH pairs: for twenty years that was the only way to look leftward. XLOOKUP does it in one function, and that is the second half of the argument.

The approximate-match trap

This is the single most common source of wrong answers in spreadsheets, and it is a default rather than a mistake.

VLOOKUP’s fourth argument controls match type, and omitting it means approximate:

=VLOOKUP(102, A2:D4, 3)          ← approximate. Almost never what you want.
=VLOOKUP(102, A2:D4, 3, FALSE)   ← exact. What you meant.

On unsorted data, approximate match does not fail loudly — it returns the closest preceding row, which looks entirely plausible. A lookup table sorted differently next quarter produces different answers from an unchanged formula.

XLOOKUP is exact by default. There is no fourth argument to forget.

Not found, without the wrapper

VLOOKUP returns #N/A when there is no match, so real-world formulas end up wrapped:

=IFERROR(VLOOKUP(999,A2:D4,3,FALSE),"not found")

XLOOKUP takes the fallback as an argument:

=XLOOKUP(999,A2:A4,C2:C4,"not found")

Both return not found. The second is one function instead of two, and it fails only for a missing match — IFERROR swallows every error, including the #REF! that would have told you the range is broken. That is a real difference in how much your spreadsheet is allowed to hide from you.

The one good reason to still write VLOOKUP

XLOOKUP does not exist in Excel 2019 or earlier. A file that uses it opens there with #NAME? — not a wrong value, a dead formula.

Microsoft 365 and Excel 2021 have it. Google Sheets has it. But if your spreadsheet goes to a finance team on a perpetual 2019 licence, or into a template strangers will open in unknown versions, VLOOKUP with FALSE as its fourth argument is the compatible choice and there is nothing wrong with it.

That is the whole list. Every other argument favours XLOOKUP.

Should you rewrite the ones you have?

Mostly no. A VLOOKUP with an explicit FALSE and a short column count is correct and will keep being correct. Rewriting working formulas is a good way to introduce an error into something that had none.

Worth rewriting:

• Formulas with a large column index — VLOOKUP(x, A:AZ, 34, FALSE) is a bomb waiting for someone to insert a column.
• Anything that has already broken once after a column change.
• Lookups currently written as INDEX/MATCH purely to search leftward — XLOOKUP says the same thing more legibly.

Leave alone: short, exact, stable lookups. They are fine.

How VisiGrid handles both

Every example on this page was executed against VisiGrid’s engine (0.22.0) with vgrid calc, not transcribed from another vendor’s documentation.

VLOOKUP matches Excel for exact match on same-typed values, approximate match, the omitted fourth argument, wildcards, and the no-match case — including the approximate-match trap described above, which reproduces exactly. The same wildcard rules apply to HLOOKUP, MATCH, COUNTIF and SUMIF, so a pattern that works in one works in all of them.

XLOOKUP matches Excel across the board: the lookup and return ranges, if_not_found, wildcards (*, ?, and ~ to escape them), every search mode — forward, reverse and both binary variants — both approximate match modes, and multi-column return ranges, which spill across cells rather than collapsing to the first column.

Type handling is the same across both, and the same as Excel. A lookup matches on type as well as value, in either direction:

So rewriting a VLOOKUP as an XLOOKUP cannot change the answer through a type mismatch. When a column arrived as text, convert it — VALUE() to numbers or TEXT(…,"@") to text, whichever matches the data you are searching. See why a VLOOKUP fails.

COUNTIF and SUMIF do match numeric-looking text, because their criteria argument is a pattern language rather than a lookup value. That is Excel’s design too.

The approximate-match behaviour this page warns about is VLOOKUP’s, and it works here exactly as Excel does — which is what makes the warning worth heeding.