What it does
XLOOKUP takes the range to search and the range to return as two separate arguments. The return column can sit to the left or the right of the lookup column, you never count columns, and inserting a column does not break the formula. Exact match is the default.
The fourth argument is the text to show when nothing is found, so you do not need IFERROR. The fifth argument turns on approximate match (next smaller or next larger) and wildcards; the sixth starts the search from the bottom, which finds the latest record for a code.
If the return range has several columns, the result spills into the cells to the right and one formula brings back several fields of a row. For files that will be opened in Excel 2019 or earlier, use VLOOKUP or INDEX with MATCH; to return every row that meets a condition, use FILTER.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=ÇAPRAZARA(aranan; arama_dizisi; döndürülen_dizi; [bulunamazsa]; [eşleştirme_modu]; [arama_modu])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value you are looking for, or the cell that holds it (e.g. E2). The search is not case-sensitive. |
lookup_array | Required | The single column or row to search, e.g. B2:B6. |
return_array | Required | The range to return from; it must have as many rows as lookup_array. Give it several columns and the result spills to the right. |
if_not_found | Optional | The value returned when there is no match, e.g. "Not found". If omitted, #N/A is returned. |
match_mode | Optional | 0 or omitted: exact match. -1: exact match or the next smaller value. 1: exact match or the next larger value. 2: wildcard match with * and ?. |
search_mode | Optional | 1 or omitted: search from first to last. -1: search from last to first, which finds the last match. 2 and -2: binary search on a sorted list (for speed on very large lists). |
Examples
1Return the name to the left of an employee ID
The name column (A) is to the left of the ID column (B), which VLOOKUP cannot handle. The formula in F2 is filled down: the ID in column E is searched in B and the name from A is returned.
=XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6,"Not found")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Full Name | Employee ID | Department | ID to Find | Full Name | |
| 2 | Emma Clark | P-1038 | Accounting | P-1042 | Liam Turner | |
| 3 | Liam Turner | P-1042 | Sales | P-1099 | Not found | |
| 4 | Olivia Hughes | P-1047 | Warehouse | |||
| 5 | Noah Bennett | P-1051 | Sales | |||
| 6 | Ava Collins | P-1063 | Human Resources |
- F2 (filled down to F3)
=XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6,"Not found")
P-1042 returns Liam Turner. P-1099 is not in the list, so F3 shows Not found; no extra function is needed to catch the error.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Find the latest price of a product
Price changes are listed in date order and a product appears more than once. With search_mode -1 the search starts from the bottom and finds the last record. The fourth argument shows "No price" if the code is missing.
=XLOOKUP(E2,B2:B7,C2:C7,"No price",0,-1)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Product Code | Unit Price | Product Code | Current Price | |
| 2 | 1-Jul-2026 | COF-250 | 185.00 | COF-250 | 212.75 | |
| 3 | 1-Jul-2026 | TEA-500 | 142.50 | |||
| 4 | 15-Aug-2026 | COF-250 | 198.00 | |||
| 5 | 1-Sep-2026 | TEA-500 | 149.90 | |||
| 6 | 1-Oct-2026 | COF-250 | 212.75 | |||
| 7 | 20-Oct-2026 | SUG-1000 | 64.00 |
COF-250 appears three times; because the search starts at the bottom, the latest price from 1 October comes back: 212.75. Without the sixth argument you would get the first price from July.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Find a discount band from the order amount
The discount table is in E:F. With match_mode -1, XLOOKUP takes the nearest lower bound when there is no exact match. The fourth argument is left empty (,,).
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Order Amount | Discount | From | Discount | |
| 2 | West Build | 4,800 | 0% | 0 | 0% | |
| 3 | North Market | 12,500 | 5% | 5,000 | 3% | |
| 4 | Atlas Ltd | 25,000 | 8% | 10,000 | 5% | |
| 5 | Blue Electric | 61,300 | 10% | 25,000 | 8% | |
| 6 | 50,000 | 10% |
- C2 (filled down to C5)
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1)
North Market's order of 12,500 has no exact match, so the formula takes the next lower bound, 10,000, and the discount is 5%. Atlas Ltd is at exactly 25,000 and gets 8%.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: No match was found and if_not_found was left empty. If the value looks present, it may have a trailing space, or one list stores it as a number and the other as text.
Fix: Add a message as the fourth argument; clean spaces with TRIM and convert numbers stored as text into numbers.
- #VALUE!
Cause: lookup_array and return_array are different sizes, e.g.
B2:B6andA2:A7.Fix: Start and end both ranges on the same rows.
- #SPILL!
Cause: The return range has several columns and one of the cells the result needs is not empty.
Fix: Clear the cells to the right of the formula, or return a single column.
Good to know
- With approximate match (-1 or 1) the list does not need to be sorted; the sorting rule of VLOOKUP does not apply here.
- To bring back several fields from the same row, give a wider return range:
=XLOOKUP(F2,A2:A6,B2:D6)returns three columns at once.
In Nexell
Get Nexell to do it from a sentence
Add each employee's full name to the leave list from the staff list by employee ID, and write "Not found" where the ID is missing
Convert to tableBring both lists into the tool and type this. Nexell builds the match with a real Excel lookup formula and the preview shows which rows did not match. The formula stays live in the downloaded file.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between XLOOKUP and VLOOKUP?
VLOOKUP takes one table range and a column number, only looks to the right and uses approximate match by default. XLOOKUP takes the lookup and return ranges separately, can look to the left, uses exact match by default, shows its own message for missing values and can search from the bottom. XLOOKUP exists only in Microsoft 365 and Excel 2021 or later.
Can XLOOKUP return more than one match?
No; XLOOKUP returns only the first match (or the last one with search_mode -1). To list every row that meets a condition, use FILTER.
Related functions
Step-by-step guide: Excel automation without macros: lookups, pivots and formatting
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- XMATCHÇAPRAZEŞLEŞTİR · Position of a value; exact match by default.
- INDEXİNDİS · Returns the value at a given row and column of a range.
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- IFERROREĞERHATA · Returns a fallback if the value is an error.