The Excel XLOOKUP function searches a range for a value and returns the matching item from another range. It is the modern replacement for VLOOKUP, HLOOKUP, and most INDEX/MATCH formulas — it looks in any direction, defaults to exact match, handles missing values gracefully, and can return entire rows or columns at once.
E2 in column A and return the matching value from column C:
#N/A — add a fourth argument like "Not found" to replace that error with friendly text.
Syntax
| Argument | Description | |
|---|---|---|
lookup_value | Required | The value to search for. |
lookup_array | Required | The range or array to search in. One row or one column. |
return_array | Required | The range or array to return values from. Must be the same length as lookup_array; can be multiple columns or rows (the result spills). |
if_not_found | Optional | Value to return when no match is found. Without it, XLOOKUP returns #N/A. |
match_mode | Optional | 0 = exact match (default) · -1 = exact or next smaller · 1 = exact or next larger · 2 = wildcard match (*, ?, ~). |
search_mode | Optional | 1 = search first-to-last (default) · -1 = search last-to-first · 2 = binary search ascending · -2 = binary search descending. |
The two arguments that matter most: 95% of XLOOKUP formulas only use the first three arguments plus if_not_found. Unlike VLOOKUP, the default is an exact match, so there is no FALSE to remember.
Basic exact-match lookup
The worksheet below holds a small employee table. To find Maria Lopez's department, look her name up in column A and return the matching value from column B:
| A | B | C | E | F | ||
|---|---|---|---|---|---|---|
| 1 | Name | Department | Salary | Lookup | Result | |
| 2 | James Chen | Sales | $71,200 | Maria Lopez | Marketing | |
| 3 | Maria Lopez | Marketing | $68,400 | |||
| 4 | Devon Smith | Sales | $74,800 | |||
| 5 | Priya Patel | Finance | $82,100 | |||
| 6 | Alex Rivera | IT | $79,500 |
The formula in F2 is:
Read it left to right as a sentence: "Find E2 (Maria Lopez) in A2:A6, and give me the matching value from B2:B6." Change B2:B6 to C2:C6 and the same formula returns her salary instead. No column counting, no FALSE, no broken formulas when someone inserts a column.
Try it: interactive XLOOKUP demo
Build an XLOOKUP against the employee table above. Change the inputs and watch the formula and result update — including what happens when the name doesn't exist.
Handling "not found" gracefully
When XLOOKUP can't find the lookup value, it returns the #N/A error. With VLOOKUP you needed to wrap the whole formula in IFERROR; XLOOKUP builds the fix in as the fourth argument:
Tip: if_not_found accepts anything — text, a number, "" for blank, or even another formula. A common pattern is a fallback lookup: =XLOOKUP(E2, A2:A6, B2:B6, XLOOKUP(E2, OldList, OldDepts, "Not anywhere")).
Left lookup (the VLOOKUP killer)
VLOOKUP can only return values to the right of the lookup column. XLOOKUP doesn't care about direction, because the lookup range and return range are separate arguments. Given IDs in column C and names in column A, you can look up an ID and return the name to its left:
| A | B | C | E | F | ||
|---|---|---|---|---|---|---|
| 1 | Name | Department | Emp ID | Lookup ID | Name | |
| 2 | James Chen | Sales | E-1042 | E-1077 | Priya Patel | |
| 3 | Maria Lopez | Marketing | E-1058 | |||
| 4 | Devon Smith | Sales | E-1063 | |||
| 5 | Priya Patel | Finance | E-1077 | |||
| 6 | Alex Rivera | IT | E-1090 |
Approximate match: tiers, brackets, and rates
Commission tiers, tax brackets, shipping bands, volume discounts — these all need "find the bracket this number falls into," not an exact match. Set match_mode to -1 (exact match or next smaller value):
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Sales at least | Commission | Sales | Rate | |
| 2 | $0 | 2% | $83,500 | 6% | |
| 3 | $25,000 | 4% | |||
| 4 | $50,000 | 6% | |||
| 5 | $100,000 | 8% |
$83,500 isn't in the table, so XLOOKUP takes the next smaller value, $50,000, and returns its rate. Unlike VLOOKUP's approximate mode, the table does not need to be sorted (sorting is only required if you also use binary search_mode).
Drag the sales amount and watch which tier match_mode -1 lands on.
More power: spill, wildcards, and last match
Return several columns at once (spill)
Make return_array wider than one column and a single formula returns the whole row, spilling across adjacent cells:
Wildcard match
Set match_mode to 2 to use wildcards: * for any characters, ? for one character:
Find the LAST match
By default XLOOKUP returns the first match from the top. Set search_mode to -1 to search bottom-up — perfect for "most recent transaction" when data is in date order:
Two-way lookup (row AND column)
Nest one XLOOKUP inside another to match both a row and a column — a cleaner replacement for INDEX/MATCH/MATCH:
The inner XLOOKUP finds the right column, and hands it to the outer XLOOKUP as the return array; the outer one finds the right row within it.
XLOOKUP vs VLOOKUP
If you know VLOOKUP, here is the whole story in one table:
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Default match type | Exact | Approximate (silent wrong answers if you forget FALSE) |
| Look left of the lookup column | Yes | No |
| Built-in "not found" message | Yes — 4th argument | No — needs IFERROR wrapper |
| Survives inserting/deleting columns | Yes — no column numbers | No — col_index_num breaks |
| Horizontal lookup | Yes — same function | No — separate HLOOKUP |
| Search from the bottom | Yes — search_mode -1 | No |
| Return multiple columns | Yes — spills | No |
| Works in Excel 2019 and older | No | Yes |
Translating an old formula is mechanical. This VLOOKUP:
becomes this XLOOKUP — point at the two columns directly instead of counting to "2":
Errors & common pitfalls
#N/A — value not found. The most common result. Causes: the value truly isn't there, or it "looks" there but differs invisibly — extra spaces ("Maria Lopez "), text-formatted numbers vs real numbers, or smart quotes from pasted data. Fix the data with TRIM or convert text numbers, or supply if_not_found to handle legitimate misses.
#VALUE! — mismatched array sizes. lookup_array and return_array must be the same length. =XLOOKUP(E2, A2:A6, B2:B7) fails because A has 5 cells and B has 6.
#SPILL! — no room for results. When the return array is multiple columns/rows, the result needs empty cells to spill into. Clear whatever is blocking the spill range.
Pitfall: locking ranges before you copy. If you copy the formula down a column, lock the arrays with absolute references: =XLOOKUP(E2, $A$2:$A$6, $B$2:$B$6). Otherwise the ranges shift as you copy and rows near the bottom silently miss matches. (Excel tables — Ctrl+T — avoid this entirely with structured references.)
Pitfall: binary search modes. search_mode 2 and -2 are fast on huge sorted data but return wrong answers without any error on unsorted data. Unless you have hundreds of thousands of rows and a sorted table, leave search_mode alone.
Pitfall: it's not in Excel 2019. XLOOKUP requires Excel 2021, Excel 365, or Excel for the web. If your file will be opened in older versions, those users see #NAME?. Use INDEX/MATCH or VLOOKUP for backward compatibility.
Practice workbook
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
What versions of Excel have XLOOKUP?
Why does my XLOOKUP return #N/A when the value is clearly there?
Can XLOOKUP return multiple columns?
Can XLOOKUP look up based on two criteria?
Is XLOOKUP faster or slower than VLOOKUP?
What replaces VLOOKUP's TRUE / approximate match?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class