Skip to content

Excel function · Lookup & reference

Excel XLOOKUP function

XLOOKUP finds a value in one column and returns the value in the same position of another range: a name from an employee ID, the latest price from a product code. It is available in Microsoft 365 and Excel 2021.

In Turkish Excel: ÇAPRAZARA

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

English Excel=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Turkish Excel=Ç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

ArgumentRequiredDescription
lookup_valueRequiredThe value you are looking for, or the cell that holds it (e.g. E2). The search is not case-sensitive.
lookup_arrayRequiredThe single column or row to search, e.g. B2:B6.
return_arrayRequiredThe 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_foundOptionalThe value returned when there is no match, e.g. "Not found". If omitted, #N/A is returned.
match_modeOptional0 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_modeOptional1 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.

F2fx=XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6,"Not found")
Return the name to the left of an employee ID: example table
ABCDEF
1Full NameEmployee IDDepartmentID to FindFull Name
2Emma ClarkP-1038AccountingP-1042Liam Turner
3Liam TurnerP-1042SalesP-1099Not found
4Olivia HughesP-1047Warehouse
5Noah BennettP-1051Sales
6Ava CollinsP-1063Human 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.

F2fx=XLOOKUP(E2,B2:B7,C2:C7,"No price",0,-1)
Find the latest price of a product: example table
ABCDEF
1DateProduct CodeUnit PriceProduct CodeCurrent Price
21-Jul-2026COF-250185.00COF-250212.75
31-Jul-2026TEA-500142.50
415-Aug-2026COF-250198.00
51-Sep-2026TEA-500149.90
61-Oct-2026COF-250212.75
720-Oct-2026SUG-100064.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 (,,).

C2fx=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1)
Find a discount band from the order amount: example table
ABCDEF
1CustomerOrder AmountDiscountFromDiscount
2West Build4,8000%00%
3North Market12,5005%5,0003%
4Atlas Ltd25,0008%10,0005%
5Blue Electric61,30010%25,0008%
650,00010%
  • 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:B6 and A2: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.

#NAME?

Cause: The file was opened in a version without XLOOKUP (Excel 2019 or earlier).

Fix: Use INDEX + MATCH or VLOOKUP for those versions.

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

Type this in Nexell

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 table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free