Lookup and sum an entire column or row dynamically (SUM + XLOOKUP)

How XLOOKUP returns a whole vertical slice when given a 2D return grid

When lookup_array is a 1-row horizontal header B1:D1 ("Q1", "Q2", "Q3") and return_array is a 2D numeric block B2:D4, XLOOKUP("Q2", B1:D1, B2:D4) returns the ENTIRE matched vertical column C2:C4!

Wrapping that vertical array directly inside SUM, AVERAGE, or MAX lets dropdown-driven dashboards switch metrics or time periods without fragile OFFSET or INDIRECT strings.

Dynamic column SUM and AVERAGE formulas

Pass 0 as XLOOKUP's 4th argument (if_not_found) so an unrecognized period header returns 0 instead of #N/A.

=SUM(XLOOKUP(F2, B1:D1, B2:D4, 0))

Dynamic row SUM by matching vertical row label

Flip orientation to sum across Q1..Q3 for whichever Region is chosen.

=SUM(XLOOKUP("West", A2:A4, B2:D4, 0))

Additional worked calculation

Using the initial example data:

=AVERAGE(XLOOKUP(F2, B1:D1, B2:D4, 0))

Inputs and interpretation

This sums only the bounded data block, not every worksheet row or column. Headers and row keys should be unique; otherwise the first match wins. A zero fallback intentionally conflates an unknown label with a real zero total. Numeric blanks and errors need an explicit data-cleaning policy.

Further reading

• Microsoft Support — XLOOKUP function