XLOOKUP vs VLOOKUP: Which Lookup to Use

Excel & Google Sheets

How it works

XLOOKUP searches A2:A5 for the value in E2 and returns whatever sits in the same row of C2:C5 — the same answer =VLOOKUP(E2,A2:C5,3,FALSE) gives, with three fewer ways to get it wrong. Exact match is XLOOKUP's default, so there's no FALSE argument to forget; VLOOKUP without FALSE silently returns wrong values on unsorted data. XLOOKUP can look left — the return column can sit anywhere relative to the search column, while VLOOKUP only reads to the right of column one. And because XLOOKUP takes a return range instead of a counted column number, inserting a column never breaks it. The one reason VLOOKUP still matters: XLOOKUP needs Excel 2021/365 or Google Sheets, so files shared with older Excel still need VLOOKUP or INDEX+MATCH.

E2

The value to find — a SKU, invoice number, or customer name.

A2:A5

The column to search. Unlike VLOOKUP, it doesn't have to be the leftmost column.

C2:C5

The column to return from — pointed at directly, no column counting.

When to use it

Default to XLOOKUP in Excel 2021+, Microsoft 365, and Google Sheets. Reach for VLOOKUP only when the file must open cleanly in Excel 2019 or older — where XLOOKUP shows #NAME?.

Common mistakes

• ✕Adding FALSE to XLOOKUP out of VLOOKUP habit.

  ✓XLOOKUP's fourth argument is the if-not-found value, not a match mode. =XLOOKUP(E2,A2:A5,C2:C5,FALSE) shows FALSE when there's no match. Use text there instead: =XLOOKUP(E2,A2:A5,C2:C5,"Not found").

• ✕Keeping the column-number habit.

  ✓XLOOKUP wants a return range, not an index. =XLOOKUP(E2,A2:A5,3) fails — point at the column: =XLOOKUP(E2,A2:A5,C2:C5).

• ✕Converting a shared workbook and breaking it for old-Excel users.

  ✓Anyone on Excel 2019 or older sees #NAME? on every XLOOKUP. If the file circulates outside your team, keep VLOOKUP with FALSE, or use INDEX+MATCH.