Lookup & Reference
Sum, Max & Average a Variable Column by Header (CHOOSECOLS + MATCH)
Select a numeric column by a quarter header and calculate its sum, maximum, minimum and average with missing-header checks.
Switch KPI summary columns dynamically with a single dropdown cell!
MATCH finds the position of F2 within B1:D1; CHOOSECOLS extracts the corresponding column from B2:D4. This is the three-row example range, not an entire worksheet column.
This example requires a nonempty text header and all three values in the selected column to be numeric. Blank, numeric-text and error values are rejected rather than omitted from the average. Zero and negative values remain valid.
Ordinary exact-match MATCH is case-insensitive and takes the first duplicate. Keep headers unique. The example uses simple Q1-style labels; wildcard syntax in user-entered headers is outside its intended input convention.
| Store | Q1 (Col B) | Q2 (Col C) | Q3 (Col D) |
|---|---|---|---|
| North | 120 | 150 | 180 |
| South | 90 | 210 (Q2 MAX) | 160 |
| West | 140 | 180 | 220 (Q3 MAX) |
| Column SUM | 350 | 540 (F2 = "Q2") | 560 (F2 = "Q3") |
Dynamic Column Sum by Matched Header Name
Extracts column MATCH(F2, B1:D1, 0) from data block B2:D4 and totals all rows.
=IF(OR(NOT(ISTEXT(F2)),COUNTBLANK(F2)>0),"Enter header",IF(IFNA(MATCH(F2,B1:D1,0),0)=0,"Header not found",IF(COUNT(CHOOSECOLS(B2:D4,MATCH(F2,B1:D1,0)))<>3,"Invalid data",SUM(CHOOSECOLS(B2:D4, MATCH(F2, B1:D1, 0))))))Maximum of the selected column
Wraps the identical CHOOSECOLS slice in MAX or AVERAGE.
=IF(OR(NOT(ISTEXT(F2)),COUNTBLANK(F2)>0),"Enter header",IF(IFNA(MATCH(F2,B1:D1,0),0)=0,"Header not found",IF(COUNT(CHOOSECOLS(B2:D4,MATCH(F2,B1:D1,0)))<>3,"Invalid data",MAX(CHOOSECOLS(B2:D4, MATCH(F2, B1:D1, 0))))))Average of the selected column
All three numeric rows contribute; the displayed average is rounded to two decimals.
=IF(OR(NOT(ISTEXT(F2)),COUNTBLANK(F2)>0),"Enter header",IF(IFNA(MATCH(F2,B1:D1,0),0)=0,"Header not found",IF(COUNT(CHOOSECOLS(B2:D4,MATCH(F2,B1:D1,0)))<>3,"Invalid data",ROUND(AVERAGE(CHOOSECOLS(B2:D4, MATCH(F2, B1:D1, 0))), 2))))Minimum of the selected column
Use the same validated selection for the minimum.
=IF(OR(NOT(ISTEXT(F2)),COUNTBLANK(F2)>0),"Enter header",IF(IFNA(MATCH(F2,B1:D1,0),0)=0,"Header not found",IF(COUNT(CHOOSECOLS(B2:D4,MATCH(F2,B1:D1,0)))<>3,"Invalid data",MIN(CHOOSECOLS(B2:D4,MATCH(F2,B1:D1,0))))))Try the example
Use the original values or change them to test your understanding.