Succeeded by XLOOKUP

Use XLOOKUP, unless the file has to work in older Excel.

VLOOKUP has had a long career and it still works. But since Excel 2021 there is a successor that does the same job with less counting and fewer silent mistakes: XLOOKUP.

Which one should I use?

Everyone who opens the file has Excel 2021 or Microsoft 365.
Use XLOOKUP.
The file must also work in Excel 2019 or older, for example a client's file or a shared template.
Use VLOOKUP.
You are editing an old workbook full of VLOOKUPs.
Leave formulas that work alone, and switch when you rework that part.

Go to XLOOKUP

Why XLOOKUP is better

  • It looks to the left too. No more moving columns so the key sits on the far left.
  • No column number to count. You point at the column you want back, so inserting a column does not break the formula.
  • Exact match is the default. Forget the FALSE at the end of VLOOKUP and it returns a wrong value without a warning; XLOOKUP does not.
  • A built-in message for missing values, without wrapping the formula in IFERROR.
  • It can search from the bottom up, to find the latest posting.

VLOOKUP (every version)

=VLOOKUP(E2, A2:C6, 3, FALSE)
=VLOOKUP(E2; A2:C6; 3; FALSE)

XLOOKUP (Excel 2021 and Microsoft 365)

=XLOOKUP(E2, A2:A6, C2:C6)
=XLOOKUP(E2; A2:A6; C2:C6)

Need VLOOKUP for an older file?

The full VLOOKUP page has the syntax, the arguments, the common mistakes and four worked examples, for files that have to work in Excel 2019 or older.

Open the full VLOOKUP page