XLOOKUP vs VLOOKUP: Which Lookup Function Should You Use?

By the XLsheetAI Team · Updated August 7, 2026 · 8 min read

The one-sentence answer

If your Excel version has XLOOKUP (Microsoft 365, Excel 2021 or later, or Google Sheets), use XLOOKUP; fall back to VLOOKUP only when a workbook must stay editable for people on Excel 2019 or older. That single rule settles most of the debate, because XLOOKUP does everything VLOOKUP does with fewer traps.

The rest of this article covers the details behind that rule: exact syntax, the six concrete ways XLOOKUP is better, the situations where VLOOKUP or INDEX+MATCH is still the right call, and how to migrate existing formulas without breaking anything.

Syntax, side by side

VLOOKUP takes a value, a whole table, a column number counted from the table's left edge, and an optional match-type flag. XLOOKUP takes a value, the column to search, and the column to return, plus three optional arguments. The crucial difference: VLOOKUP defaults to approximate match, XLOOKUP defaults to exact.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=XLOOKUP(lookup_value, lookup_array, return_array,
         [if_not_found], [match_mode], [search_mode])

That fourth VLOOKUP argument is the classic bug factory. If you omit [range_lookup], it defaults to TRUE, meaning approximate match. On unsorted data that quietly returns wrong values rather than an error, and the mistake can sit in a report for months. Nearly every VLOOKUP you write should end in FALSE, and forgetting it is easy.

XLOOKUP inverts the defaults. Leave everything optional out and you get an exact match, with #N/A when the value is missing. You opt in to approximate matching with match_mode (-1 for next smaller, 1 for next larger, 2 for wildcards) instead of getting it by accident.

Six concrete advantages of XLOOKUP

XLOOKUP beats VLOOKUP in six practical ways: it searches in any direction including left, matches exactly by default, survives inserted columns, handles missing values with a built-in argument, can search from the bottom up, and can return whole ranges rather than a single cell. Each one removes a real, recurring failure mode.

1. It searches any direction, including left. VLOOKUP can only return values from columns to the right of the lookup column. If your ID lives in column D and the name you need lives in column A, VLOOKUP forces a helper column or a rearranged table. XLOOKUP simply takes the two ranges in either order.
2. Exact match is the default. No more silent wrong answers because someone omitted FALSE. You get the value you asked for, or #N/A.
3. No column-index breakage. Because there is no col_index_num, inserting or deleting columns cannot redirect the formula to the wrong field. The range references shift with the sheet, like every other Excel reference.
4. Built-in if_not_found. The fourth argument replaces the ubiquitous IFERROR(VLOOKUP(...), "Not found") wrapper, which is shorter and safer, since IFERROR also swallows unrelated errors you actually want to see.
5. Reverse search. Set search_mode to -1 and XLOOKUP scans last-to-first, returning the most recent entry in a log or transaction list. VLOOKUP always returns the first match, full stop.
6. It can return arrays and ranges. Point return_array at several columns and XLOOKUP spills the entire matching row. You can even use one XLOOKUP inside another to return a two-dimensional block, something VLOOKUP cannot approximate.

Not sure which formula fits your case? Describe the lookup in plain English — "get the price for this SKU, show Not found if it's missing" — and XLsheetAI writes the correct XLOOKUP, VLOOKUP, or INDEX+MATCH for you, ready to paste.

When VLOOKUP is still the right call

Choose VLOOKUP when the workbook must stay editable for people running Excel 2019, 2016, or older, when you maintain legacy templates full of existing VLOOKUPs, or when a formula will be pasted into unknown environments. XLOOKUP produces a #NAME? error on those versions, and a broken shared file costs more than an elegant formula saves.

Compatibility is the whole argument, but it is a strong one. Perpetual-license Excel 2019 and 2016 remain common in corporate environments and on older machines, and Microsoft never backported XLOOKUP to them. Send a supplier a workbook built on XLOOKUP and, if they are on 2016, every lookup cell shows an error they cannot fix.

Legacy templates are the second case. A model with two hundred working VLOOKUPs gains nothing from a bulk rewrite; you spend an afternoon to introduce risk. Migrate opportunistically, when a formula already needs editing.

Google Sheets users can mostly ignore this section. Sheets gained XLOOKUP in 2022 and updates for everyone simultaneously, so there is no version split. The only Sheets-side caution is round-tripping: if a Sheet will be exported to .xlsx and opened in old desktop Excel, the same compatibility limits apply to the exported file.

INDEX+MATCH: the third option

INDEX+MATCH pairs two functions that exist in every version of Excel: MATCH finds the row position, INDEX returns the value at that position. It matches most of XLOOKUP's flexibility, including left lookups, while remaining compatible with Excel 2016, 2019, and everything older. Its cost is readability: two nested functions instead of one.

=INDEX(C2:C10, MATCH(E2, A2:A10, 0))

INDEX+MATCH wins when you need XLOOKUP-style behavior in a file that old Excel versions must open. It looks left, survives inserted columns, and can be extended with a second MATCH to find the column dynamically, a genuinely powerful pattern for two-way lookups. Note MATCH's own trap: like VLOOKUP, it defaults to approximate matching, so always pass 0 as the third argument for an exact match.

The drawback is human, not technical. Colleagues who have never seen the pattern find it opaque, and a mistyped range pair (mismatched heights between the INDEX and MATCH ranges) fails in confusing ways. If everyone in the file has modern Excel, XLOOKUP gives you the same power in plainer form. For a refresher on these and other essentials, see our Excel formulas cheat sheet.

How to replace VLOOKUP with XLOOKUP: the same lookup, three ways

Migration is mechanical: the VLOOKUP's lookup value stays put, the table's first column becomes lookup_array, and the column that col_index_num pointed to becomes return_array. Any IFERROR wrapper folds into if_not_found. Here is one dataset, SKUs in A2:A10, product names in B2:B10, prices in C2:C10, written all three ways.

Read across the first row and the trade-off is visible at a glance. The VLOOKUP is compact but hides two hazards: the hard-coded 3 and the mandatory FALSE. The XLOOKUP names both ranges explicitly. The INDEX+MATCH does the same job with one more layer of nesting.

Performance and compatibility caveats

For everyday tables, up to tens of thousands of rows, all three approaches recalculate fast enough that you will not notice a difference. The real caveats are elsewhere: version support, binary search on huge datasets, and what happens when files travel between modern and legacy Excel installs.

On version support, the line is sharp. XLOOKUP exists in Microsoft 365, Excel 2021 and later, Excel for the web, and Google Sheets (since 2022). It does not exist in Excel 2019, 2016, or anything earlier, and opening a modern workbook there turns each XLOOKUP cell into a #NAME? error with the last calculated value frozen in place.

On speed at scale, sorted data changes the picture. VLOOKUP with TRUE performs a binary search, and XLOOKUP matches that with search_mode 2 (or -2 descending). On a million-row sorted table, binary search is orders of magnitude faster than a linear exact-match scan. If a workbook full of lookups feels sluggish, sorting the source data and switching to binary mode helps more than swapping function names.

One more habit worth keeping regardless of function: reference tight ranges or Table columns rather than whole columns like A:A, especially in workbooks with many formulas. And if your source data is trapped in a screenshot or a printout rather than a sheet at all, convert the image to Excel first so your lookups have real cells to work with.

3 mistakes people make when switching

Most XLOOKUP migrations go wrong in one of three ways: mismatched range sizes between the lookup and return arrays, forgetting that colleagues on older Excel cannot open the result, and dropping error handling because the IFERROR wrapper disappeared. All three are quick to avoid once you know to look for them.

• Mismatched array heights. lookup_array and return_array must be the same size. Pairing A2:A100 with C2:C99 returns #VALUE!. When converting a VLOOKUP, take both ranges from the same rows of the original table_array.
• Forgetting your audience's Excel version. The formula works on your machine, then a manager on Excel 2019 opens the file to a wall of #NAME? errors. Check where a shared workbook will be opened before modernizing it.
• Losing the not-found handling. People strip IFERROR(VLOOKUP(...)) down to a bare XLOOKUP and reintroduce #N/A into dashboards. Move the fallback text into the if_not_found argument instead of deleting it.

Bottom line

XLOOKUP is the better function and the right default for anyone on Microsoft 365, Excel 2021+, or Google Sheets. VLOOKUP survives on compatibility: reach for it when older Excel versions must open your file, and reach for INDEX+MATCH when you need left lookups in those same legacy environments. Migrate old formulas gradually, as you touch them, rather than all at once.

Practice makes these stick. XLsheetAI turns plain-English requests into working lookup formulas and explains any formula you paste in, so you can learn XLOOKUP hands-on while keeping legacy VLOOKUPs readable.