Cellwise

Excel guide · 3 min read

VLOOKUP vs XLOOKUP

XLOOKUP is the better function if everyone using the file has Excel 365 or 2021 and later. It defaults to an exact match, can look left, has a built-in "not found" value, and does not break when you insert columns. Use VLOOKUP only when the file must work in Excel 2019 or earlier.

Sample data

Every formula on this page was calculated live against this sheet.

ABCD
1Emp IDNameDeptSalary
2E-104Rosa DiazSupport52000
3E-117Ken ItoEngineering98000
4E-121Ama BoatengFinance74500
5E-130Pete NovakSupport49000
6E-142Lily ChenEngineering105000

The same lookup, written both ways

VLOOKUP takes the whole table and a column number. The FALSE at the end asks for an exact match.

XLOOKUP takes the lookup column and the return column directly: =XLOOKUP("E-121",A2:A6,D2:D6). No column counting, and exact match is the default.

fx=VLOOKUP("E-121",A2:D6,4,FALSE)

Result: 74500

Returns 74500, the salary from column 4 of the table.

Difference 1: the default match type

Leave the last argument out of VLOOKUP and it does an approximate match. On unsorted data that can quietly return the wrong row with no error. This is the most common VLOOKUP bug.

XLOOKUP does an exact match unless you ask for something else, which is what most lookups need.

Difference 2: looking to the left

VLOOKUP can only search the first column of the table and return something to its right. To find an ID from a name, you would have to rearrange columns or switch to INDEX MATCH. XLOOKUP looks in any direction.

fx=XLOOKUP("Ken Ito",B2:B6,A2:A6)

Result: E-117

Returns "E-117", from the column to the left of the names.

Difference 3: handling missing values

When a value is missing, VLOOKUP shows #N/A and you need IFERROR around it. XLOOKUP has a fourth argument for the message.

fx=XLOOKUP("E-999",A2:A6,B2:B6,"No such employee")

Result: No such employee

Returns "No such employee" instead of #N/A.

Difference 4: inserting columns and other extras

VLOOKUP's column number is hard-coded. Insert a column inside the table and the 4 now points at the wrong data. XLOOKUP refers to the return column itself, so it adjusts automatically.

XLOOKUP can also search from the bottom up to get the last match (search_mode -1), return several columns at once by giving a multi-column return range, and do wildcard matches with match_mode 2. VLOOKUP supports wildcards in exact-match mode without any extra setting.

fx=XLOOKUP("Support",C2:C6,B2:B6,"",0,-1)

Result: Pete Novak

Returns "Pete Novak", the last Support employee in the list.

Which one should you use?

Use XLOOKUP for new work in Excel 365, Excel 2021, Excel 2024 or Excel for the web. Use VLOOKUP, or INDEX MATCH, when you share files with people on Excel 2019 or earlier. They will see #NAME? where every XLOOKUP is.

Learn to read both. Most existing workbooks are full of VLOOKUP, and XLOOKUP is what you will write going forward.

Common mistakes

Leaving FALSE off VLOOKUP

Always end exact lookups with FALSE (or 0). Otherwise VLOOKUP does an approximate match and may return a wrong value.

XLOOKUP ranges of different sizes

The lookup and return ranges must have the same number of rows, or XLOOKUP returns #VALUE!.

Sending XLOOKUP to older Excel

Excel 2019 and earlier show #NAME?. Check your audience, or use INDEX MATCH.

FAQ

Is XLOOKUP better than VLOOKUP?

For most people, yes. It is safer by default and more flexible. VLOOKUP's only real advantage is that it works in every version of Excel.

Is XLOOKUP slower than VLOOKUP?

For normal workbooks you will not notice a difference. On very large data, an approximate or binary-search lookup on sorted data is fastest with either function.

Does XLOOKUP replace HLOOKUP too?

Yes. XLOOKUP works on rows as well as columns, so one function covers both VLOOKUP and HLOOKUP.

Which Excel versions have XLOOKUP?

Excel for Microsoft 365, Excel 2021, Excel 2024, Excel for the web and the mobile apps. Excel 2019 and 2016 do not.

Functions used

Keep learning

Updated 2026-10-31