Lookup & Reference
Dynamic two-way sum and average: aggregate a chosen column by row criteria
Select a monthly metric column with XLOOKUP, filter its rows, and calculate a dynamic sum or average.
Two-way aggregation when row labels repeat
A simple two-way lookup stops at the first matching row. When a budget table has multiple "Cloud" cost centers down Column A and months Jan/Feb/Mar across Row 1, you need to SUM every "Cloud" row inside whichever month column the user picks.
XLOOKUP selects the chosen month column as an array. FILTER keeps the selected line’s rows, then SUM or AVERAGE aggregates those values. Cloud in February totals 150 + 110 = 260.
| Line (A) | Jan (B) | Feb (C) | Mar (D) |
|---|---|---|---|
| Cloud | 120 | 150 | 180 |
| Support | 40 | 50 | 60 |
| Cloud | 80 | 110 | 130 |
SUM with FILTER and XLOOKUP, or a SUMPRODUCT matrix formula
The SUMPRODUCT alternative combines a row mask and a column mask over the numeric matrix. With duplicate headers, XLOOKUP selects the first matching column while SUMPRODUCT includes every matching header; require unique headers when those results should agree.
=SUM(FILTER(XLOOKUP(F2, B1:D1, B2:D4), A2:A4=E2, 0))Dynamic two-way average
AVERAGE over the filtered array gives (150 + 110) / 2 = 130. An unmatched line returns zero for the SUM variant, but has no defined average; handle that case explicitly.
=AVERAGE(FILTER(XLOOKUP(F2, B1:D1, B2:D4), A2:A4=E2))Try the example
Use the original values or change them to test your understanding.