VLOOKUP has been Excel's go-to lookup function for three decades. XLOOKUP arrived in 2019 to fix everything that's clumsy about it. The honest question everyone eventually asks: should I just stop using VLOOKUP altogether? Mostly yes — but with a few real caveats it'd be bad to ignore.
The 30-second answer
For new formulas where everyone using the workbook is on Microsoft 365 or Excel 2021+, use XLOOKUP. It's clearer, faster, more flexible, and doesn't have the silent failure modes that bite people with VLOOKUP.
For workbooks you'll share with people on older Excel — or that might end up in Google Sheets — keep VLOOKUP. XLOOKUP doesn't exist there and the cells show #NAME? errors. Not ideal.
That's the short version. The long version, with the actual reasoning, follows.
What VLOOKUP actually does
VLOOKUP looks up a value in the leftmost column of a range and returns the value from another column on the same row. The syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])A concrete example. Your price list lives in A2:C100, with product names in A, descriptions in B, and prices in C. To find the price of "Widget":
=VLOOKUP("Widget", A2:C100, 3, FALSE)This searches column A for "Widget," finds the row, and returns the value in column C. The FALSE at the end means "exact match only" — leave it off and VLOOKUP defaults to approximate match. We'll come back to that.
What's wrong with VLOOKUP
Five things that make it more fragile than it should be.
1. It only looks to the right
VLOOKUP demands the lookup key be in the leftmost column of your table. If your data has the key in column C and you want a value from column A, VLOOKUP literally cannot do it. You either restructure your data or fall back to INDEX/MATCH.
2. The return column is a magic number
VLOOKUP(key, A2:F100, 4, FALSE). Quick — what's in column 4 of that table? You can't tell without going back and counting. Worse: if someone inserts a new column inside the range, your "4" now points at a different column entirely. Your formula doesn't error. It silently returns wrong values. This is the bug that's chewed up the most spreadsheets in the world.
3. It defaults to approximate match
If you forget the fourth argument, VLOOKUP defaults to TRUE: approximate match. For sorted numeric tables (tax brackets, shipping tiers) that's intentional. For unsorted text data it returns wrong values, silently, with no error. Many a junior analyst has been embarrassed by this on a Monday morning.
4. It's slow on big tables
With exact match, VLOOKUP scans the table from top to bottom for every formula. In a workbook with thousands of VLOOKUP formulas running against a 100,000-row table, this gets expensive fast. Recalc times that used to be sub-second start hitting tens of seconds.
5. Nothing built-in for "not found"
If the lookup misses, you get #N/A. You wrap the whole call in IFERROR or IFNA to handle that case cleanly — which adds nesting and makes the formula uglier.
What XLOOKUP fixes
Full syntax:
=XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode], [search_mode])The same Widget lookup, with XLOOKUP:
=XLOOKUP("Widget", A2:A100, C2:C100, "Not found")Look at what changed:
- The lookup column and return column are separate arguments. You can return from any column — left, right, doesn't matter. The leftmost-column-must-be-the-key tyranny is over.
- No column index number. You point at the actual column you want by reference. Insert a column in between? Your formula still works.
- Exact match is the default. You have to deliberately ask for approximate match. The silent wrong-answer bug is gone.
- Built-in "not found" argument. No more wrapping in IFERROR.
- Reverse search is optional. Set search_mode to -1 to search from the bottom up — useful for "find the most recent entry."
Performance
For exact-match lookups on small tables, XLOOKUP and VLOOKUP perform about the same. On large tables (say 50K+ rows), XLOOKUP with binary search (search_mode 2 on pre-sorted data) is dramatically faster than VLOOKUP with exact match. If you've got a workbook with heavy lookups against a big table, switching can cut your recalc time by half or more.
Side-by-side examples
Look up an order ID, return the customer name
Order table: ID, Date, CustomerName, Amount (columns A–D).
=VLOOKUP(F2, A2:D1000, 3, FALSE)
=XLOOKUP(F2, A2:A1000, C2:C1000)XLOOKUP wins on readability — you can see at a glance that it returns from the CustomerName column. With VLOOKUP you have to count.
Look up by customer name, return the order ID
VLOOKUP can't do this — the ID is to the left of the name. You'd need INDEX/MATCH:
=INDEX(A2:A1000, MATCH(F2, C2:C1000, 0))
=XLOOKUP(F2, C2:C1000, A2:A1000)XLOOKUP is the same shape regardless of direction.
Look up with a graceful "Not found"
=IFERROR(VLOOKUP(F2, A2:D1000, 3, FALSE), "Not found")
=XLOOKUP(F2, A2:A1000, C2:C1000, "Not found")XLOOKUP folds error handling into the call. One less layer of nesting.
When VLOOKUP is still the right choice
Sharing with people on older Excel
XLOOKUP was added in Microsoft 365 and Excel 2021. Anyone on Excel 2019 or older — and there are still plenty in larger US organizations and government — will see #NAME? instead of your XLOOKUP result. If your workbook travels widely outside your immediate team, VLOOKUP is the safer choice.
Templates that might land in Google Sheets
Google Sheets implements VLOOKUP but only added XLOOKUP relatively recently and inconsistently. If your spreadsheet might get uploaded to Sheets, test there first or keep VLOOKUP for portability.
Approximate-match lookups on sorted tables
For tax brackets (think federal income tax tables), shipping tiers, and similar sorted numeric ranges, VLOOKUP with TRUE has been the standard for decades and works fine. XLOOKUP can do it too with match_mode 1, but there's no real win to switching here.
And INDEX/MATCH?
Before XLOOKUP, the canonical workaround for VLOOKUP's left-lookup limitation was INDEX/MATCH. It's still in millions of workbooks. It works fine, it's available in every version of Excel ever shipped, and there's no need to rip it out — but for new formulas in modern Excel, XLOOKUP is shorter and clearer than INDEX/MATCH.
The rule of thumb
Default to XLOOKUP for everything new. Use VLOOKUP only when you have a real compatibility reason. Leave existing VLOOKUPs alone unless you're already editing the formula for another reason — there's no upside to rewriting working code just to use the newer function.
Yes, even after thirty years, VLOOKUP isn't going anywhere. But for new spreadsheets you're authoring today, XLOOKUP is just better in essentially every way that matters.