XLOOKUP tricks
Assumed knowledge:
- Standard
XLOOKUP.
XLOOKUP with multiple search criteria
Generic formula:
=XLOOKUP(1,(column1=criteria1)*(column2=criteria2)*(column3=criteria3),array_to_return_data)
The 1 represents 'all values true'.
XLOOKUP with dynamic lookup initial value or dynamic results column
This is done by nesting one XLOOKUP inside another.
You can use this instead of an INDIRECT formula (which is powerless to know if you've renamed anything referenced in the formula).
Vary the column that the results are pulled from
Use this when your initial lookup values are the same (or pulled from the same column) but the return array should vary.
Generic formula, assuming the table's name is Table1:
=XLOOKUP(criterion,[column_to_search_for_criterion],XLOOKUP("column_to_fetch_results_from",Table1[#Headers],Table1))
Notice that "column_to_fetch_results_from" is a string, not a column reference. We want the formula to find the column with the same name as "column_to_fetch_results_from" and pull the results from there. (How you feed that string to the formula is out of scope here.)
A possible scenario:
We want to populate the 'Number of appendages' column in this table...
| Name | Type | Value to look up | Number of appendages |
|---|---|---|---|
| Birgette | Car | Number of wheels | formula goes here |
| Briga | Lizard | Number of legs | formula goes here |
| Brita | Motorbike | Number of wheels | formula goes here |
| Ederitta | Hedgehog | Number of legs | formula goes here |
| Jenny | Spider | Number of legs | formula goes here |
| Malia | Snake | Number of legs | formula goes here |
| Poron | Pigeon | Number of legs | formula goes here |
| Rubite | Car | Number of wheels | formula goes here |
| Trevionel | Frog | Number of legs | formula goes here |
| Vallacca | Millipede | Number of legs | formula goes here |
...with values pulled from this other table (call it tableOfStuff).
| Name | Type | Number of legs | Number of wheels |
|---|---|---|---|
| Birgette | Car | 0 | 4 |
| Briga | Lizard | 4 | 0 |
| Brita | Motorbike | 0 | 2 |
| Ederitta | Hedgehog | 4 | 0 |
| Jenny | Spider | 8 | 0 |
| Malia | Snake | 0 | 0 |
| Poron | Pigeon | 2 | 0 |
| Rubite | Car | 0 | 3 |
| Trevionel | Frog | 4 | 0 |
| Vallacca | Millipede | 40 | 0 |
The formula:
=XLOOKUP([@[Name]],tableOfStuff[Name],XLOOKUP([@[Value to look up]],tableOfStuff[#Headers],tableOfStuff),"Not found")
Notes:
"Not found"is optional; this is what the formula will display instead of a generic error.
Vary the initial lookup
=XLOOKUP(criterion,XLOOKUP("column_to_search_for_criterion",Table1[#Headers],Table1),Table1[Column_to_fetch_results_from])
Notice that "column_to_search_for_criterion" is a string, not a column reference. We want the formula to find the column with the same name as "column_to_search_for_criterion" and search for criterion in there. (How you feed that string to the formula is out of scope here.)