XLOOKUP multiple criteria
AdvancedIn real-world scenarios, you often need to look up data based on multiple criteria. While XLOOKUP is typically used for single-criterion lookups, you can nest XLOOKUP functions to handle two-dimensional lookups.
The key insight is that XLOOKUP's return_array parameter can itself be an XLOOKUP result. This creates a powerful two-way lookup:
=XLOOKUP(row_lookup, row_range, XLOOKUP(col_lookup, col_headers, data_range))
The inner XLOOKUP finds the correct column, returning an entire column of data. The outer XLOOKUP then finds the correct row within that column.
Your task
You have a pricing table with products in column A and quarterly prices in columns B through E. The goal is to build a dynamic lookup that returns the price for any product in any quarter.
In cell H2, enter the product name "Widget D". In cell H3, enter the quarter "Q3".
In cell H5, write a nested XLOOKUP formula that:
- Uses the product in H2 to find the correct row
- Uses the quarter in H3 to find the correct column
- Returns the price at the intersection
The formula should work for any product and quarter combination.
Need some help?
Hint 1
Start by thinking about what each XLOOKUP needs to do: one finds the row (product), one finds the column (quarter).
Hint 2
The inner XLOOKUP should search the quarter headers (B1:E1) and return the corresponding column from the data range (B2:E8).
Hint 3
Structure: =XLOOKUP(product_cell, product_range, XLOOKUP(quarter_cell, quarter_headers, data_range))
Answer
Exercise
XLOOKUP multiple criteria
AdvancedIn real-world scenarios, you often need to look up data based on multiple criteria. While XLOOKUP is typically used for single-criterion lookups, you can nest XLOOKUP functions to handle two-dimensional lookups.
The key insight is that XLOOKUP's return_array parameter can itself be an XLOOKUP result. This creates a powerful two-way lookup:
=XLOOKUP(row_lookup, row_range, XLOOKUP(col_lookup, col_headers, data_range))
The inner XLOOKUP finds the correct column, returning an entire column of data. The outer XLOOKUP then finds the correct row within that column.
Your task
You have a pricing table with products in column A and quarterly prices in columns B through E. The goal is to build a dynamic lookup that returns the price for any product in any quarter.
In cell H2, enter the product name "Widget D". In cell H3, enter the quarter "Q3".
In cell H5, write a nested XLOOKUP formula that:
- Uses the product in H2 to find the correct row
- Uses the quarter in H3 to find the correct column
- Returns the price at the intersection
The formula should work for any product and quarter combination.
Need some help?
Hint 1
Start by thinking about what each XLOOKUP needs to do: one finds the row (product), one finds the column (quarter).
Hint 2
The inner XLOOKUP should search the quarter headers (B1:E1) and return the corresponding column from the data range (B2:E8).
Hint 3
Structure: =XLOOKUP(product_cell, product_range, XLOOKUP(quarter_cell, quarter_headers, data_range))