XLOOKUP is the modern replacement for VLOOKUP — it looks left, defaults to exact match, handles missing values without a wrapper, and returns whole arrays. But VLOOKUP still runs everywhere, including older Excel and Google Sheets. Below, every difference is measured on identical data in a live engine, not copied from a feature list. Edit the cells and watch both formulas re-evaluate side by side.
If your workbook will only ever open in Excel 2021+ or Microsoft 365, use XLOOKUP: it is safer by default and does strictly more. If the file must open in Excel 2019 or earlier, LibreOffice, or older Google Sheets builds, VLOOKUP (or INDEX/MATCH) is the portable choice. There is no scenario where VLOOKUP is more capable than XLOOKUP — its only advantages are ubiquity and muscle memory.
Fourteen capabilities, scored honestly. ✓ full support, ◐ partial / with caveats, ✗ not supported. VLOOKUP's only green cells are ubiquity — every capability edge goes to XLOOKUP.
| Capability | VLOOKUP | XLOOKUP | Why it matters |
|---|---|---|---|
| Look up a value to the right | ✓ Yes | ✓ Yes | The baseline case — both tie. |
| Look up a value to the left | ✗ No | ✓ Yes | VLOOKUP can only return columns at or right of the table's first column. |
| Default match mode | ◐ Approximate (TRUE) | ✓ Exact (0) | VLOOKUP's silent approximate default is the #1 wrong-answer trap on unsorted data. |
| Handle a missing value | ◐ #N/A + wrapper | ✓ if_not_found arg | XLOOKUP's 4th argument replaces IFERROR/IFNA. |
| Return several columns at once | ✗ One per formula | ✓ Dynamic array | XLOOKUP spills a whole return range. |
| Search direction (first / last match) | ✗ First only | ✓ 1 or -1 | Matters with duplicate keys. |
| Approximate match direction | ◐ Next smaller only | ✓ -1 or +1 | XLOOKUP can round up or down explicitly. |
| Survives inserting a column | ✗ Index breaks | ✓ Range auto-shifts | VLOOKUP hardcodes col_index_num; XLOOKUP names the return range. |
| Return a whole row / vertical array | ✗ No | ✓ Yes | XLOOKUP's return range can be 2-D. |
| Wildcard match (* ?) | ✓ Yes | ✓ Yes (mode 2) | Both support wildcards; XLOOKUP needs match_mode 2. |
| Binary search on sorted data | ◐ Approximate only | ✓ search_mode 2/-2 | XLOOKUP exposes binary search explicitly. |
| Excel 2019 and earlier | ✓ Yes | ✗ No (#NAME?) | XLOOKUP needs Excel 2021 / Microsoft 365. |
| Google Sheets | ✓ Yes | ✓ Yes | Both supported in current Google Sheets. |
| LibreOffice Calc | ✓ Yes | ◐ 7.2+ only | Older LibreOffice builds lack XLOOKUP. |
The structural difference: VLOOKUP counts columns from a fixed table edge; XLOOKUP names the lookup and return ranges independently.
One editable dataset, two formulas. Pick a scenario or type your own — both sides re-evaluate in the same engine, so you see exactly where they diverge.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=VLOOKUP(103,A2:E6,4,FALSE)=XLOOKUP(103,A2:A6,D2:D6)=VLOOKUP("Carol",B2:E6,1,FALSE)=XLOOKUP("Carol",B2:B6,A2:A6)=VLOOKUP(103,A2:E6,4)=XLOOKUP(103,A2:A6,D2:D6)=VLOOKUP(999,A2:E6,4,FALSE)=XLOOKUP(999,A2:A6,D2:D6,"Not found")=VLOOKUP(103,A2:E6,2,FALSE)=XLOOKUP(103,A2:A6,B2:D6)=VLOOKUP(103.5,A2:E6,4,TRUE)=XLOOKUP(103.5,A2:A6,D2:D6,"",-1)=VLOOKUP("a",A2:B5,2,FALSE)=XLOOKUP("a",A2:A5,B2:B5,"",0,-1)500 exact-match lookups against tables of increasing size, timed in this page's engine.
| Table size | VLOOKUP | XLOOKUP |
|---|---|---|
| 1,000 rows × 500 lookups | 383.16 ms | 363.51 ms |
| 5,000 rows × 500 lookups | 4237.71 ms | 4319.24 ms |
| 10,000 rows × 500 lookups | 15639.3 ms | 15689.95 ms |
A decision rule for the situations that actually come up. The short version: XLOOKUP for new controlled-environment work, VLOOKUP (or INDEX/MATCH) when the file must open elsewhere.
| Situation | Use | Why |
|---|---|---|
| Building a new workbook for Excel 2021 / Microsoft 365 | XLOOKUP | XLOOKUP is safer by default and strictly more capable. No reason to choose VLOOKUP here. |
| The file must open in Excel 2019 or earlier | VLOOKUP | XLOOKUP returns #NAME? on old builds. Use VLOOKUP, or INDEX/MATCH for left lookups. |
| You need to return a column to the LEFT of the key | XLOOKUP | VLOOKUP structurally cannot. XLOOKUP does it natively; the old workaround is INDEX/MATCH. |
| Sharing with mixed / unknown Excel versions | VLOOKUP | VLOOKUP is the lowest common denominator. INDEX/MATCH if you also need left lookups. |
| The lookup key has duplicates and you want the LAST match | XLOOKUP | XLOOKUP search_mode -1. VLOOKUP always returns the first. |
| You want a clean default instead of #N/A on missing keys | XLOOKUP | XLOOKUP's if_not_found argument. With VLOOKUP you wrap in IFNA/IFERROR. |
| Returning several adjacent columns for one key | XLOOKUP | One XLOOKUP spills the whole block; VLOOKUP needs one formula per column. |
| Maximum raw speed on a huge sorted table | Either | Engine-dependent and usually marginal. In our HyperFormula benchmark the two were within a few percent. Measure your own data. |
The six translations you will actually make. Note how the IFNA wrapper and the INDEX/MATCH workaround both collapse into a single XLOOKUP.
| Task | VLOOKUP / old way | XLOOKUP | What changed |
|---|---|---|---|
| Exact match, return right column | =VLOOKUP(A2, D2:G100, 3, FALSE) | =XLOOKUP(A2, D2:D100, F2:F100) | Drop the column index; name the return column. Exact is now the default. |
| Missing value → blank instead of #N/A | =IFNA(VLOOKUP(A2, D2:G100, 3, FALSE), "") | =XLOOKUP(A2, D2:D100, F2:F100, "") | The IFNA wrapper collapses into the 4th argument. |
| Left lookup (return a column left of the key) | =INDEX(A2:A100, MATCH(E2, B2:B100, 0)) | =XLOOKUP(E2, B2:B100, A2:A100) | The INDEX/MATCH workaround becomes a single XLOOKUP. |
| Approximate match, next smaller | =VLOOKUP(A2, D2:G100, 3, TRUE) | =XLOOKUP(A2, D2:D100, F2:F100, "", -1) | match_mode -1 makes the direction explicit; data must still be sorted. |
| Return three columns at once | =VLOOKUP(A2,D2:G100,2,FALSE) & …3… & …4… | =XLOOKUP(A2, D2:D100, E2:G100) | One formula spills three columns instead of three separate VLOOKUPs. |
| Last match among duplicates | (not possible — first match only) | =XLOOKUP(A2, D2:D100, F2:F100, "", 0, -1) | search_mode -1 searches bottom-up. |