Lookup
XLOOKUP in Excel:syntax, examples and errors
XLOOKUP looks up a value in a column or row and returns the value in the same position of another, to the left or to the right. In Spanish Excel it is called BUSCARX. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]).
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
lookup_value | Required. What you are looking for. Example: F2. |
lookup_array | Required. The column (or row) where what you are looking for sits. Example: A2:A6. |
return_array | Required. The column (or row) the result comes from. It has to have the same number of rows as lookup_array. Example: D2:D6. |
if_not_found | Optional. What it shows if the value is not found. Without it, you get #N/A. Example: "Not found". |
match_mode | Optional. 0 (or empty) is exact. -1 looks for the exact value or the next smaller (bands). 1, the exact or the next larger. 2 allows wildcards such as *. |
search_mode | Optional. 1 (or empty) searches from top to bottom. -1 searches from bottom to top, to bring back the last match. |
How do you use XLOOKUP? A step-by-step example
XLOOKUP looks up a value in one column and brings back the value from another column in the same row. It is the same catalog as in VLOOKUP: you want the price of the code you type in F2.
| A | B | C | D | F | ||
|---|---|---|---|---|---|---|
| 1 | Code | Product | Category | Price | Look up | |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 | P-103 | |
| 3 | P-102 | A5 notebook | Stationery | 2.30 | ||
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 | ||
| 5 | P-104 | Office chair | Furniture | 89.00 | ||
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
=XLOOKUP(F2,A2:A6,D2:D6) returns 6.8.
F2is what you look for: P-103.A2:A6is where you look: the column of codes.D2:D6is where the result comes from: the column of prices.
There is no column number and no FALSE at the end: XLOOKUP looks for the exact value by default.
What else can XLOOKUP do?
| You want | Formula | Result |
|---|---|---|
| A message if it does not exist | =XLOOKUP("P-999",A2:A6,D2:D6,"Not found") | Not found |
| Look to the left | =XLOOKUP("Office chair",B2:B6,A2:A6) | P-104 |
| Look up part of the text | =XLOOKUP("Desk*",B2:B6,D2:D6,"Not found",2) | 24.5 |
| Look up bands | =XLOOKUP(7200,H2:H5,I2:I5,,-1), with the commission table from VLOOKUP | 0.05 |
| Look up in a row | =XLOOKUP("Feb",L1:N1,L3:N3), with the months in row 1 | 5 |
In the bands lookup, -1 means "the exact value or, if it is not there, the next smaller". Unlike VLOOKUP, the table does not have to be sorted.
XLOOKUP or VLOOKUP?
XLOOKUP, if your Excel has it and so does whoever will open the file. If you do not know, use VLOOKUP or INDEX and MATCH.
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Versions | Microsoft 365, Excel 2021 and later, Excel for the web | All |
| Direction | Any, also to the left and across rows | Only to the right |
| Default match | Exact | Approximate, if you leave out the last argument |
| If it does not find the value | Fourth argument | You have to add IFERROR |
| If you insert a column in the table | Keeps working | The column number points to another one |
| Last match | Yes, with search_mode -1 | No |
How does XLOOKUP show up in an Excel test?
In a job interview you can be asked for the same lookup both ways: "Bring in the product name from the code; now the code from the name." With XLOOKUP it is the same formula with the columns swapped. If you use VLOOKUP for the second one in the test, it does not work: the code is to the left.
In a skills test:
What does
=XLOOKUP("P-999",A2:A6,D2:D6)return if P-999 is not in A2:A6? a) 0 · b) An empty cell · c) #N/A · d) #REF!
Answer: c. Without the fourth argument, XLOOKUP gives #N/A when it does not find the value. To count or add the rows that match instead, see COUNTIF.
Practice XLOOKUP
The English exercises are on the way. Until then, copy the catalog into a blank sheet and look up a code by name, then by code, using the same formula with the two columns swapped.
Available in: Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not in Excel 2019 or earlier. Source: Microsoft: XLOOKUP function. Results calculated with Formulo's engine.
Common mistakes
- #NAME?: your Excel does not have XLOOKUP
- XLOOKUP is only in Microsoft 365, Excel 2021 and later, and Excel for the web. In Excel 2019 or earlier the formula gives #NAME?. Use VLOOKUP or INDEX with MATCH.
- #N/A: it does not find the value
- The value is not there, or it has an extra space, or it is a number stored as text. =XLOOKUP(F4,A2:A6,D2:D6) gives #N/A if F4 is P-999. Add the fourth argument to show a message.
- #VALUE!: the two columns have different sizes
- lookup_array and return_array have to have the same number of rows. A2:A6 with D2:D7 gives #VALUE!.
- When you share the file, it shows #NAME?
- Whoever opens it has an Excel older than 2021. If you do not know which version the other person has, use VLOOKUP or INDEX and MATCH.
Frequently asked questions
What is XLOOKUP called in Spanish?
BUSCARX. In a Spanish Excel: =BUSCARX(F2;A2:A6;D2:D6).
Why is XLOOKUP not in my Excel?
Because your version is Excel 2019 or earlier. XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web.
Is XLOOKUP better than VLOOKUP?
If your Excel has it, yes for almost everything: it looks in any direction, it is exact by default and it has the message built in. VLOOKUP is still useful for files that someone with an older Excel will open.
How do I bring back the last value with XLOOKUP?
With the sixth argument at -1, which searches from bottom to top: =XLOOKUP(F2,A2:A50,C2:C50,,0,-1) returns the last row where F2 appears.
Can XLOOKUP return several columns at once?
Yes. If return_array has several columns, such as B2:D6, the result fills several cells to the right. Leave those cells free or you get #SPILL!.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.