XLOOKUP is the modern replacement for VLOOKUP. It is easier to write, more flexible and harder to break. Here is how they compare.
Syntax side by side
VLOOKUP: =VLOOKUP(F2, A2:C100, 3, FALSE)
XLOOKUP: =XLOOKUP(F2, A2:A100, C2:C100, "Not found")
With XLOOKUP you select the column to search and the column to return separately, so there is no column number to count.
Key differences
- Look left: XLOOKUP can return a column to the left of the lookup column. VLOOKUP cannot.
- Exact match by default: XLOOKUP matches exactly unless you say otherwise. VLOOKUP needs
FALSEor it may return a wrong approximate match. - No column number: inserting or deleting columns does not break XLOOKUP.
- Built-in error text: the fourth argument replaces IFERROR for missing values.
- Search from the bottom: XLOOKUP can find the last match, useful for latest records.
When to still use VLOOKUP
XLOOKUP is available in Microsoft 365, Excel 2021 and later. If you share files with people on Excel 2016 or 2019, VLOOKUP is the safe choice because those versions cannot calculate XLOOKUP.
Which should you learn?
Learn both. Interviews still ask about VLOOKUP, and many company files use it, but use XLOOKUP in your own work whenever your Excel supports it.