VLOOKUP vs XLOOKUP: Which One Should You Use? (2026 Guide)
Both formulas answer the same question: "I have a value — find it in this table and bring back the matching row." VLOOKUP has done that job since the 1980s. XLOOKUP arrived in 2019 and fixed most of VLOOKUP's annoyances. Here is the honest comparison, with the same example in both, so you can decide for your spreadsheet.
The same lookup, both ways
You have a product list: column A is the product code, column C is the price. Find the price of product ABC123.
VLOOKUP:
=VLOOKUP("ABC123",A2:D100,3,FALSE)XLOOKUP:
=XLOOKUP("ABC123",A2:A100,C2:C100)Read VLOOKUP as: find ABC123 in A2:D100, return the value from the 3rd column of that range, and use an exact match (FALSE). Read XLOOKUP as: find ABC123 in A2:A100, return the value from the same position in C2:C100. Same answer, but XLOOKUP says what it means — no counting columns, no cryptic FALSE at the end.
Side by side
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Exact match | Only if you add FALSE — forget it and you get an approximate match, which is wrong most of the time | Exact match by default |
| Look to the left | Impossible — the lookup column must be the leftmost column of the range | Works — the lookup and return ranges are separate |
| Inserting a column | Breaks it — the column number you counted no longer points at the right column | Safe — ranges shift with the sheet |
| Not found | #N/A, unless you wrap it in IFERROR | Built in: =XLOOKUP("ABC123",A2:A100,C2:C100,"Not found") |
| Return a whole row | One column at a time | One formula can return several columns at once |
| Works in | Every Excel version, Google Sheets, LibreOffice | Microsoft 365 and Excel 2021 or newer only — not Google Sheets |
When XLOOKUP wins
Use XLOOKUP when you are on Microsoft 365 or Excel 2021 and the file stays in modern Excel. The built-in "not found" text alone is worth it — #N/A errors scattered through a report are the number one reason people wrap VLOOKUP in IFERROR, and XLOOKUP makes that wrapper unnecessary:
=XLOOKUP("ABC123",A2:A100,C2:C100,"Not found")Looking to the left is the other big one. If the code is in column C and the price is in column A, VLOOKUP simply cannot do it; XLOOKUP does not care which side the answer is on.
When to keep VLOOKUP
- The file is shared with people on old Excel. XLOOKUP shows as
#NAME?in Excel 2019 and older. If your file goes to clients, auditors, or anyone whose Excel version you do not control, VLOOKUP is the safe choice. - Google Sheets. XLOOKUP does not exist there. VLOOKUP works, and so does INDEX and MATCH.
- Existing spreadsheets that already work. Rewriting a working VLOOKUP for fashion is how working reports break. Leave it alone.
The mistake both of them share
Neither function fixes dirty data. If VLOOKUP or XLOOKUP returns #N/A for a value you can see in the table, the values only look the same. Check with =A2=B2 in a spare cell — FALSE means a hidden difference, usually a trailing space or a number stored as text. Trim spaces with =TRIM(A2) and convert text-numbers with Data → Text to Columns → Finish.
FAQ
Is XLOOKUP better than VLOOKUP?
In most cases, yes — XLOOKUP defaults to an exact match, can look to the left, and has a built-in "if not found" value. But VLOOKUP still wins on compatibility: it works in every version of Excel and in Google Sheets, while XLOOKUP needs Microsoft 365 or Excel 2021 or newer.
Why does VLOOKUP return #N/A?
Usually the lookup value genuinely is not in the table, or it does not match exactly — extra spaces, numbers stored as text, or a different format. Check with =A2=B2 in a spare cell: FALSE means the values only look the same.
Does XLOOKUP work in Google Sheets?
No — XLOOKUP is not available in Google Sheets. Use VLOOKUP or INDEX and MATCH there.
Describe it — get the formula
Type what you want in plain English and get the VLOOKUP, XLOOKUP, or any other formula, explained. Free: 5 formulas a day, no account. Sigline Pro ($39 once) unlocks unlimited.