XLOOKUP in Excel: how to use it
With XLOOKUP you point to where Excel should look and where the answer is. It works in both directions and matches exactly by default. You do need Excel 2021 or 365.
Try the first exercises for freeIn the lesson Looking up with XLOOKUP
What does XLOOKUP do?
XLOOKUP looks up a value in one column and gives you what's in another column on the same row. You simply point to the two columns: where Excel should look, and where the answer is.
So you don't need to count columns, the answer can be to the left or the right of the lookup column, and XLOOKUP looks for an exact match by default. With the fourth argument you give your own message straight away, for when the value isn't found.
When do you use XLOOKUP?
- The price for a code: =XLOOKUP(E2,A2:A50,C2:C50).
- The code for a product name, so looking to the left: =XLOOKUP(E2,B2:B50,A2:A50).
- Your own message when something is missing: =XLOOKUP(E2,A2:A50,C2:C50,"Unknown").
- The discount for an amount that falls between two limits: =XLOOKUP(B2,F2:F5,G2:G5,0,-1).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =XLOOKUP("Monitor",B2:B5,A2:A5,"Unknown")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
XLOOKUP | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | XLOOKUP |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
lookup_value | What you're looking for. | "Monitor" |
lookup_array | The column or row you search in. | B2:B5 |
return_array | The column or row with the answer. | A2:A5 |
[if_not_found] | Optional: what you want to see if the value isn't there. | "Unknown" |
[match_mode] | Optional: 0 for an exact match (the default), -1 for the next smaller value and 1 for the next larger value. | left out |
[search_mode] | Optional: -1 to start at the bottom. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
XLOOKUP step by step
E1 contains =XLOOKUP("Monitor",B2:B5,A2:A5,"Unknown"). Looks to the left, which VLOOKUP can't do. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket.
=XLOOKUP( - Type what you're looking for: "Monitor". Type a comma.
=XLOOKUP("Monitor", - Select where Excel should look: the product names in B2:B5. Type a comma.
=XLOOKUP("Monitor",B2:B5, - Select where the answer is: the codes in A2:A5. That column can be to the left of the lookup column.
=XLOOKUP("Monitor",B2:B5,A2:A5 - Type a comma and a message for when the product doesn't exist: "Unknown". Close the bracket and press Enter: P-204.
=XLOOKUP("Monitor",B2:B5,A2:A5,"Unknown")
More examples
The price for a code
E1 contains =XLOOKUP("P-102",A2:A5,C2:C5). It looks in the codes and takes the answer from the prices. You don't need a column number.
Your own message
E1 contains =XLOOKUP("P-999",A2:A5,C2:C5,"Unknown"). The code doesn't exist. Instead of #N/A, you see the message from the fourth argument.
The next smaller value
D1 contains =XLOOKUP(750,A2:A4,B2:B4,0,-1). With -1 as the fifth argument, Excel takes the next smaller value if 750 isn't there: 500, so a 5% discount.
The advantages
No more counting column numbers, looking in any direction and a message of your own when something isn't found. With -1 as the sixth argument, Excel starts at the bottom, which is handy when you want the latest entry.
XLOOKUP or VLOOKUP?
XLOOKUP matches exactly by default, also looks to the left and doesn't break when you add a column, because you don't count columns. Someone with a version older than Excel 2021 sees #NAME?. If you share your files with lots of people, VLOOKUP or INDEX with MATCH is still the safest choice.
Fetching several columns at once
If you select several columns as the answer, such as =XLOOKUP(E2,A2:A5,B2:C5), Excel fills in the cells next to it with the product and the price. Those cells must be empty, otherwise you get #SPILL!.
Where it often goes wrong
- Doesn't work in Excel 2019 or older.
- The lookup and return arrays must be the same size.
Practise this in the lesson Looking up with XLOOKUP. The first exercises are free.
Frequently asked questions
How do I find the nearest value?
Give -1 as the fifth argument for the next smaller value or 1 for the next larger one.