XLOOKUP vs VLOOKUP: How to Use XLOOKUP (7 Examples)

Short answer: XLOOKUP is the better choice in almost every case. You give it three things, what to find, where to look and what to return: =XLOOKUP(H2, A2:A6, B2:B6). Unlike VLOOKUP, it has no column number to break when someone inserts a column, it uses exact match by default, it can look to the left, and it can show your own message instead of #N/A. Use VLOOKUP only when the file must open in Excel 2019 or older.

This guide goes with my video How to Use XLOOKUP in Excel: VLOOKUP vs XLOOKUP + 7 Examples. Every example uses a small help desk workbook, so you can rebuild it in five minutes and follow along.

Where XLOOKUP works

• Microsoft 365 (Windows and Mac), Excel 2021 and Excel 2024
• Excel for the web, and the Excel apps for iPad, iPhone and Android
• Google Sheets (it has its own XLOOKUP with the same idea)
• Not in Excel 2019 or Excel 2016. There you get #NAME?, so use INDEX and MATCH.

XLOOKUP syntax

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

Argument	Required?	What it does
lookup_value	Yes	The value you search for (an ID, an email, a code)
lookup_array	Yes	The column (or row) to search in
return_array	Yes	The column (or columns) to return from
if_not_found	No	Your own text when nothing matches, e.g. "Not found"
match_mode	No	0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcards
search_mode	No	1 first to last (default), -1 last to first, 2 / -2 binary search on sorted data

VLOOKUP vs XLOOKUP: the 4 differences that matter

Here is the same lookup both ways:

=VLOOKUP(H2, A2:F6, 2, FALSE)
=XLOOKUP(H2, A2:A6, B2:B6)

	VLOOKUP	XLOOKUP
Someone inserts a column	Column number is now wrong, no error	Ranges move with the sheet, keeps working
Default match	Approximate (if you forget FALSE)	Exact
Direction	Right only	Any direction
No match	#N/A	Your own message
Excel 2019 / 2016	Works	Not available

The first row is the one that hurts in real life. I’ve fixed client reports that were right on Monday and wrong on Tuesday, because someone added a “Phone” column and every VLOOKUP with column number 4 quietly started returning emails instead of departments. Speed is not the reason to switch: on normal office sheets you won’t feel a difference. Switch because it’s safer.

My rule from client work: if you send a workbook to people outside your company, ask which Excel they use first. If anyone is on Excel 2019 or older, keep VLOOKUP or use INDEX and MATCH in that file.

The sample data

Staff list in A1:F6:

ID	Name	Email	Dept	Laptop	Ext
1001	Abd Almouhimen Shamout	abd@mouhimen.dev	IT	LT-204	2101
1002	Abd Shamout	abd.s@mouhimen.dev	Sales	LT-118	2140
1003	Mouhimen Dev	dev@mouhimen.dev	Dev	LT-330	2155
1004	Abd A. Shamout	a.shamout@mouhimen.dev	HR	LT-087	2122
1005	A. Shamout	ashamout@mouhimen.dev	Finance	LT-412	2169

Example 1: Basic lookup with a “Not found” safety net

The ID is in H2. Return the name:

=XLOOKUP(H2, A2:A6, B2:B6, "Not found")

1003 returns Mouhimen Dev. An ID that doesn’t exist, like 1009, returns Not found instead of #N/A.

Before you copy it down: press F4 on each range to lock it ($A$2:$A$6), or turn the list into an Excel table with Ctrl+T. Table ranges grow by themselves when you add new staff.

Example 2: Look to the left

A user emails you about a password reset, and you need their ID. The email is in column C and the ID is in column A, to the left. VLOOKUP can’t do this. XLOOKUP doesn’t care:

=XLOOKUP(H2, C2:C6, A2:A6)

a.shamout@mouhimen.dev returns 1004.

Example 3: Return several columns at once

Select more than one column as the return array:

=XLOOKUP(H2, A2:A6, D2:F6)

For 1001, the result spills into three cells: IT, LT-204, 2101. One ID, and you see everything about the user. It’s perfect for a help desk card.

Example 4: Two-way lookup (row and column)

A “tickets closed” table has agents in A2:A6 and months in B1:E1. Find one agent in one month by nesting two XLOOKUPs:

=XLOOKUP(H2, A2:A6, XLOOKUP(H3, B1:E1, B2:E6))

The inner XLOOKUP finds the month (Mar) and returns that whole column. The outer one finds the agent (Abd Shamout) in it. Result: 41. The old way needed INDEX plus two MATCH functions:

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

Example 5: XLOOKUP with multiple criteria

A software licence list has the same user many times, once per app (User in A, App in B, Expiry in C, rows 2 to 9). To find the expiry for one user and one app:

=XLOOKUP(1, (A2:A9=H2)*(B2:B9=H3), C2:C9)

Each check returns TRUE or FALSE. Multiplying them turns that into 1 or 0, so only the row where both are true becomes 1, and XLOOKUP searches for that 1. For Abd Almouhimen Shamout and JetBrains it returns 2027-03-31.

Keep each check in its own brackets. Without them (A2:A9=H2*B2:B9=H3), Excel multiplies before it compares and the result is wrong. You can add a third condition the same way: *(D2:D9=H4).

Example 6: Approximate match for price tiers

Laptop discounts: 1 to 9 get 0%, 10 get 5%, 25 get 8%, 50 get 12% (min quantity in A, discount in B). An order of 30 isn’t in the table, so use match_mode -1, “exact match or the next smaller item”, and skip the fourth argument:

=XLOOKUP(H2, A2:A5, B2:B5, , -1)

30 finds 25, so the discount is 8%. Unlike VLOOKUP’s approximate match, the tiers don’t need to be sorted when you use the default search mode.

Example 7: Find the last match

An asset log lists laptop LT-204 three times: Issued, Repaired, Returned. A normal lookup returns the first row, which is old news. Set search_mode to -1 to search from the bottom up:

=XLOOKUP(H2, B2:B9, C2:C9, , 0, -1)

Result: Returned, the latest status.

Bonus, wildcards: match_mode 2 turns on wildcards. * matches any text and ? matches one character:

=XLOOKUP("Abd*", B2:B6, A2:A6, , 2)

This returns the ID of the first name that starts with “Abd” (1001).

Errors and fixes (“XLOOKUP not working”)

1. #NAME?
Your Excel doesn’t have XLOOKUP (usually 2019 or older), or the function name has a typo. Use Microsoft 365, Excel 2021+ or Excel for the web, or use this in older files:

=INDEX(B2:B6, MATCH(H2, A2:A6, 0))

2. #N/A, but you can see the value
Usually a hidden space at the end of a cell, or a number stored as text. Quick test: =H2=A4. If it says FALSE, the values are different even if they look the same. Fixes:

=XLOOKUP(H2, TRIM(A2:A6), B2:B6)      // removes extra spaces
=XLOOKUP(--H2, A2:A6, B2:B6)          // turns a text number in H2 into a real number

3. #VALUE!
Your lookup array and return array are different sizes, for example A2:A6 and B2:B7. Select the same rows in both.

4. #SPILL!
You returned several columns, but the cells next to the formula aren’t empty. Clear them and the result spills in.

Checklist

• [ ] Three required arguments: what to find, where to look, what to return
• [ ] Add "Not found" as the fourth argument in shared files
• [ ] Lock ranges with F4 or use an Excel table (Ctrl+T)
• [ ] Use -1 in match_mode for tiers, -1 in search_mode for the latest entry
• [ ] Check which Excel version your readers have before you share

FAQ

Is XLOOKUP better than VLOOKUP?
Yes, for almost every case: no column numbers, exact match by default, it looks in any direction, and it can show your own message when nothing matches. VLOOKUP’s only advantage is that it works in Excel 2019 and older.

Why is XLOOKUP not available in my Excel?
XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019 and 2016 don’t have it and show #NAME?. Use Excel for the web for free, or INDEX and MATCH.

Can XLOOKUP use multiple criteria?
Yes. Search for 1 in a multiplied set of checks: =XLOOKUP(1, (A2:A9=H2)*(B2:B9=H3), C2:C9).

Does XLOOKUP work in Google Sheets?
Yes. Google Sheets has its own XLOOKUP with the same arguments (search key, lookup range, result range, missing value, match mode, search mode).

Is XLOOKUP faster than VLOOKUP?
On normal office sheets you won’t notice a difference. Choose it because it’s safer, not because it’s faster.

Keep learning

• Stop Using Nested IFs: 3 Better Excel Formulas
• Excel TEXTJOIN: Stop Using CONCATENATE
• From IT support: Microsoft 365 Inactive Users Cleanup with PowerShell
• Next in the Excel series: How to Create a Drop-Down List in Excel (coming Fri 27 Nov)

Sources

• Microsoft Support, XLOOKUP function: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Microsoft Support, VLOOKUP function: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
• Google Docs Editors Help, XLOOKUP function: https://support.google.com/docs/answer/12405947

Checked on 30 Oct 2026.