Advanced Excel

XLOOKUP vs VLOOKUP: Which Should You Use?

By EG Digital Skills Training · Updated 18 October 2026

VLOOKUP has been the standard lookup formula for years. XLOOKUP fixes many of its weaknesses. Here is how they compare in real work.

Limits of VLOOKUP

• The value you search for must be in the first column of the table.
• It uses a column number, which breaks when someone inserts or deletes a column.
• If you forget the last argument, it may do an approximate match and return a wrong result.
• It cannot look to the left of the search column.

=VLOOKUP(F2, A2:D100, 4, FALSE)

What XLOOKUP does better

• You pick the lookup column and the return column separately.
• It looks in any direction.
• It does an exact match by default.
• It has a built-in value for when nothing is found.
• It can return several columns at once and search from last to first.

=XLOOKUP(F2, A2:A100, D2:D100, "Not found")

Version support

XLOOKUP is available in Excel 2021 and Microsoft 365. Older versions do not have it, so a workbook that uses XLOOKUP may show errors when opened in an older Excel.

When INDEX-MATCH still helps

If you share files with people on older Excel versions, INDEX and MATCH is a reliable alternative that also looks in any direction.

So which should you use?

• Use XLOOKUP when everyone who opens the file has a recent Excel.
• Use INDEX-MATCH for compatibility with older versions.
• Use VLOOKUP mainly to understand and maintain existing files.