Lookup
Finding a value by its key
Definition
Looking up a key in one column and returning a value from another means giving XLOOKUP the key, the key column, and the return column.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array)
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The key to find, in double quotes when it is text, for example "K-2". |
lookup_array |
The column of keys to scan. |
return_array |
The column of values to return from the matching row. |
Worked example
A kiln list stores keys in A2:A4 and hours in B2:B4.
| A: Key | B: Hours |
|---|---|
| K-1 | 11 |
| K-2 | 14 |
| K-3 | 8 |
Formula: =XLOOKUP("K-2",A2:A4,B2:B4)
Result: 14. K-2 is the second key, and that row holds 14 hours.
Practice
Cells A2:A4 hold id-1, id-2, and id-3. Cells B2:B4 hold 4, 9, and 6. In C2, write a formula that returns the number for id-3.
- In C2, write an XLOOKUP formula that returns the value for id-3. Keys are in A2:A4 and values are in B2:B4.
- C2 should show the value for id-3.
Step 1 of 2
Enter =XLOOKUP("id-3",A2:A4,B2:B4) in C2.
id-3 is the third row, and B4 is 6.
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 6.
The next lesson is Finding the middle of a list.
Related functions
XLOOKUP is the function that finds that key and returns the matching value.
Common mistake
Swapping the columns, as in =XLOOKUP("K-2",B2:B4,A2:A4): the formula then searches the hours and cannot return 14.