Learn · lookup
XLOOKUP vs VLOOKUP: which one should you use?
Updated 2026-08-18
Use XLOOKUP in new Microsoft 365 / Excel 2021 workbooks when everyone who opens the file has it. Keep VLOOKUP with FALSE when the file must run in Excel 2019 or earlier. That is the whole decision for most teams—the rest of this page shows how the two formulas differ on the same data, where VLOOKUP still fails, and what to verify after you switch.
This is a comparison, not a general VLOOKUP #N/A troubleshooting guide. If your current formula already errors, fix the data match first; swapping functions will not invent a key that is not in the table.
Pick the function from the file, not the blog post
| Situation | Use |
|---|---|
| Microsoft 365, Excel 2021, Excel for the web, current Sheets | XLOOKUP (default for new formulas) |
| Shared with Excel 2016 / 2019 / unknown desktop versions | VLOOKUP with FALSE, or INDEX/MATCH for left lookups |
| Return column sits to the left of the key | XLOOKUP, or INDEX/MATCH—not VLOOKUP |
| Stable workbook already full of working VLOOKUPs | Leave it. Rewrite only the formulas you are touching |
Opening an XLOOKUP workbook in Excel 2019 shows #NAME? in every converted cell. That is a version problem, not a lookup problem.
Same job, two formulas (example 1)
Product list on the sheet:
| A (SKU) | B (Name) | C (Price) |
|---|---|---|
| W-100 | Widget | 12.50 |
| W-200 | Gadget | 19.00 |
Lookup SKU in E2. When E2 is W-100, both formulas below should return 12.5.
VLOOKUP (exact match):
=VLOOKUP(E2, A2:C100, 3, FALSE)
XLOOKUP (exact match is the default; blank if missing):
=XLOOKUP(E2, A2:A100, C2:C100, "")
Sample check: put W-999 in E2. VLOOKUP returns #N/A. XLOOKUP with the fourth argument returns a blank. That is the first practical difference: missing keys are handled inside XLOOKUP instead of wrapping IFNA.
What actually changes (not marketing)
| Behavior | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match | Approximate if the 4th argument is omitted | Exact if match_mode is omitted |
| Lookup direction | First column of table_array only | Any lookup array, any return array |
| Return column | Counted col_index_num | You point at the return range |
| Not found | #N/A unless you wrap IFNA | if_not_found argument |
| Last matching row | Not built in | search_mode -1 (search last to first) |
The dangerous VLOOKUP default is still the one that bites people: omit FALSE and Excel approximate- matches against a column that is often unsorted. XLOOKUP does not do that unless you set match_mode to -1 or 1 on purpose.
Example 2 — lookup to the left
IDs are in column B; names to return are in column A. VLOOKUP cannot search column B if your table_array starts at A. This shape is common after a CSV export where the system ID is not the leftmost field.
XLOOKUP:
=XLOOKUP(E2, B2:B100, A2:A100, "ID not found")
Compatible fallback (Excel 2019 and earlier, and Sheets):
=IFNA(INDEX(A2:A100, MATCH(E2, B2:B100, 0)), "ID not found")
Sample check: put a known ID from B into E2 — you should get the name from the same row in A. A VLOOKUP of =VLOOKUP(E2, A2:B100, 1, FALSE) still searches column A, so it will miss unless the ID also happens to live in A.
Approximate match is not a drop-in rename
Grade bands and tax brackets still need an approximate lookup. VLOOKUP with TRUE requires the first column sorted ascending. XLOOKUP uses match_mode -1 (exact or next smaller) and does not rely on that same “sorted table_array” contract in the same way—but you still must understand the breakpoints.
=VLOOKUP(E2, A2:B6, 2, TRUE) =XLOOKUP(E2, A2:A6, B2:B6, "Below first band", -1)
If E2 is below the first breakpoint, VLOOKUP returns #N/A. XLOOKUP here returns the custom message. Do not copy VLOOKUP TRUE into XLOOKUP and assume the 4th argument still means “approximate.” In XLOOKUP the 4th argument is if_not_found; approximate behavior is the 5th (match_mode).
Excel vs Google Sheets
| Topic | Excel | Google Sheets |
|---|---|---|
| XLOOKUP available? | 365 / 2021 / web — not 2019 and earlier | Yes, in current Sheets |
| VLOOKUP exact match | FALSE or 0 | Same |
| Safest shared-file choice | Ask which Excel build recipients have | XLOOKUP is fine in Sheets; export to old Excel may break |
Switching without creating new bugs
- Map ranges: VLOOKUP
table_array+col_index_numbecome two ranges (lookup array and return array) of the same height. - Type
FALSEas exact in VLOOKUP; do not carry that token into XLOOKUP’s 4th argument. - Prefer
IFNAon leftover VLOOKUPs. Prefer XLOOKUP’sif_not_foundrather than wrappingIFERROR, which also hides#REF!and#VALUE!. - After paste, test a known hit, a known miss, and one row with extra spaces or a numeric-as-text ID.
Generate the formula, then run the sample checks
If the layout is clear but the argument order is not, describe the lookup cell, lookup column, and return column in plain English. Use Excel dialect for Microsoft 365, or Sheets dialect when the file lives in Google Sheets.
Modern Excel: XLOOKUP formula generator. Older workbooks: VLOOKUP formula generator. Always verify against a row you already know is correct before filling a whole column.
Related: Fix VLOOKUP #N/A·All guides
XLOOKUP vs VLOOKUP FAQs
- Should I replace every VLOOKUP with XLOOKUP?
- No. Replace it when everyone who opens the file has Excel 2021, Microsoft 365, Excel for the web, or a current Google Sheets file. Keep VLOOKUP (with FALSE) when the workbook must open in Excel 2019 or earlier without #NAME? errors.
- Does Google Sheets have XLOOKUP?
- Yes. Sheets added XLOOKUP in 2022. Syntax is close to Excel for exact matches and if_not_found. If a Sheets file must stay compatible with very old Excel exports, INDEX/MATCH or VLOOKUP is safer than assuming every desktop Excel copy can read XLOOKUP.
- Why does my converted XLOOKUP still return #N/A?
- XLOOKUP does not fix dirty data. Extra spaces, numbers stored as text, and a key that is not actually in the lookup array still miss. Use TRIM, check ISTEXT/ISNUMBER, and confirm with COUNTIF. For missing keys, set if_not_found instead of wrapping IFERROR around everything.
- Is INDEX/MATCH better than both?
- INDEX/MATCH is the compatibility option when you need a leftward lookup but cannot use XLOOKUP. It is more verbose. For new Microsoft 365 work, XLOOKUP is usually clearer. Do not rewrite a stable VLOOKUP-only workbook just to switch styles.
- What happens if someone inserts a column in a VLOOKUP table?
- col_index_num is a counted offset. Inserting a column between the lookup column and the return column silently returns the wrong field—or #REF! if the index walks off the range. XLOOKUP names the return array directly, so an insert between unrelated columns does not change which column you asked for.