XLOOKUP vs VLOOKUP: Difference, Examples and Version Support

XLOOKUP vs VLOOKUP: XLOOKUP is the newer, safer lookup. It matches exactly by default, can return a column to the left, does not break when columns are inserted and lets you set a “not found” message. VLOOKUP works in every Excel version but defaults to approximate match, looks only to the right and depends on a fixed column number. Use XLOOKUP if everyone who opens the file has Microsoft 365, Excel 2021, Excel 2024, Excel for the web or Google Sheets; otherwise use VLOOKUP with FALSE, or INDEX with MATCH.

XLOOKUP vs VLOOKUP: the difference with one example

A product master on a sheet named Products: codes in column A, product names in B, units in C and rates in D, from row 2 to row 200. On your order sheet the code is in A2 and you want the rate.

VLOOKUP: =VLOOKUP(A2,Products!$A$2:$D$200,4,FALSE)

• The code must be in the first column of the table.
• 4 means “fourth column of the table” (Rate).
• FALSE means exact match. Leave it out and VLOOKUP assumes a sorted list and approximate match, and can quietly return the wrong rate.

XLOOKUP: =XLOOKUP(A2,Products!$A$2:$A$200,Products!$D$2:$D$200,"Not found")

• You point to the column to search and the column to return separately. No column number to count or break.
• Exact match is the default.
• “Not found” replaces the #N/A error for missing codes.

Where XLOOKUP clearly wins

1. Looking left. Find a name in column B and return the code from column A: =XLOOKUP(G2,Products!$B$2:$B$200,Products!$A$2:$A$200). VLOOKUP cannot do this.
2. Several columns at once. =XLOOKUP(A2,Products!$A$2:$A$200,Products!$B$2:$D$200) returns product, unit and rate side by side, spilling into three cells.
3. Latest entry. Set search_mode to -1 to search from the bottom: useful for an employee’s most recent salary revision in a history list.
4. Safer slab lookups. For commission slabs (sales from 0, 50,000, 1,00,000 and 2,00,000 in H2:H5, rates in I2:I5), =XLOOKUP(D2,$H$2:$H$5,$I$2:$I$5,,-1) returns the rate of the band the sale falls into. match_mode -1 means “exact or next smaller”. For sales of ₹1,35,000 it returns the 1,00,000 band’s rate.
5. Survives edits. If someone inserts a column in the master, XLOOKUP still returns the right column; VLOOKUP’s fixed 4 may now point to the wrong one.

When to use VLOOKUP (or INDEX and MATCH) instead

Version is the deciding factor. If your file goes to a client, a branch office or a government department still on Excel 2016 or 2019, an XLOOKUP formula shows #NAME? there. Check your own version under File, then Account. In those cases:

• Use VLOOKUP with FALSE for simple right-side lookups: =IFNA(VLOOKUP(A2,Products!$A$2:$D$200,4,FALSE),"Not found").
• Use INDEX with MATCH when you need to look left or want a formula that survives inserted columns, in every version: =INDEX(Products!$D$2:$D$200,MATCH(A2,Products!$A$2:$A$200,0)). The 0 in MATCH means exact match; leave it out and MATCH assumes a sorted list.
• For slab tables in older versions, =VLOOKUP(D2,$H$2:$I$5,2,TRUE) works if the first column is sorted smallest to largest.

Why lookups return #N/A or wrong values

• Hidden spaces: “P003 ” is not “P003”. Clean the column with TRIM.
• Number versus text: 101 in one list and “101” in the other do not match. Convert both to the same type.
• Unlocked ranges: without $ signs, the table range shifts as you copy down. Press F4, or use an Excel table with column names.
• Missing FALSE or 0: gives plausible but wrong answers, the most dangerous kind.

Example: a furniture dealer in Jaipur used =VLOOKUP(A2,Rates!A2:D200,4) without FALSE on an unsorted list. Almirahs were billed at the chair rate until a customer noticed. Adding FALSE, locking the range and testing five known codes fixed it.

Frequently asked questions

What is the difference between XLOOKUP vs VLOOKUP?

XLOOKUP uses separate search and return columns, matches exactly by default, can look left, can show a custom not-found message and is not broken by inserted columns. VLOOKUP needs the key in the first column, uses a column number, looks only right and defaults to approximate match.

Is XLOOKUP available in Excel 2019?

No. XLOOKUP is available in Microsoft 365, Excel 2021, Excel 2024, Excel for the web and Google Sheets. In Excel 2019 or earlier it shows #NAME?, so use VLOOKUP with FALSE or INDEX with MATCH.

Should I replace all my VLOOKUPs with XLOOKUP?

Only if every person who opens the file has a version that supports XLOOKUP. Existing VLOOKUPs with FALSE and locked ranges work correctly; the bigger win is fixing any VLOOKUP that lacks FALSE.

Is INDEX MATCH better than VLOOKUP?

For flexibility, yes: INDEX with MATCH can look left and survives inserted columns, and it works in every version. It is slightly longer to write, so many offices use XLOOKUP where available and INDEX with MATCH elsewhere.

Quick quiz

Sources

• Microsoft Support: XLOOKUP function (syntax, match_mode, search_mode, availability)
• Microsoft Support: VLOOKUP, INDEX and MATCH functions
• Google Docs Editors Help: XLOOKUP function in Google Sheets

Master lookups with ready templates: Excel and AI for Office Work certificate course.

This page is for education and general information, not legal advice for a specific case. Law as on 29 October 2026.