Lookup
XLOOKUP
Definition
XLOOKUP searches a range for a key and returns the matching item from a result range.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array)
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The key to find, such as a code typed in quotes or held in a cell. |
lookup_array |
The row or column that holds the keys. |
return_array |
The row or column that holds the values to return. It must be the same size as lookup_array. |
Worked example
A nursery lists plant codes in A2:A4 and names in B2:B4.
| A: Code | B: Name |
|---|---|
| Q-1 | Maple |
| Q-2 | Birch |
| Q-3 | Cedar |
Formula: =XLOOKUP("Q-3",A2:A4,B2:B4)
Result: Cedar. Q-3 is the third code, and the name beside it is Cedar.
Practice
Range A2:A4 holds P-1, P-2, and P-3. Range B2:B4 holds Oak, Pine, and Elm. In C2, write a formula that returns the name for P-2.
- In C2, write an XLOOKUP formula that returns the name for P-2. Keys are in A2:A4 and names are in B2:B4.
- C2 should show the name for P-2.
Step 1 of 2
Enter =XLOOKUP("P-2",A2:A4,B2:B4) in C2.
P-2 is the second row, and B3 is Pine.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses XLOOKUP.
C2 now shows Pine.
Related functions
VLOOKUP needs the key in the leftmost column. HLOOKUP needs the key in the first row. XLOOKUP takes the two ranges separately.
Common mistake
Using a return range of a different height from the lookup range: XLOOKUP then returns #VALUE!.