Dynamic Arrays
Filter horizontal columns & sort left-to-right by row with FILTER and SORT
Filter a wide month-across-columns KPI table where revenue meets a threshold and sort matching columns highest-to-lowest using SORT(..., 2, -1, TRUE).
Filter and rank horizontal financial model columns without transposing twice!
This example stores month headers in B1:E1 and numeric revenue values below them. FILTER selects columns using the one-row condition B2:E2 >= B4. SORT with by_col TRUE then orders the surviving columns by their second row, highest first.
All four revenue cells and the threshold must be numeric. Zero and negative values are permitted. A threshold with no qualifying columns returns None. Equal revenues do not define a unique ranking; add a secondary ordering rule if ties must be reproducible across spreadsheet products.
| Row | Jan (B) | Feb (C) | Mar (D) | Apr (E) |
|---|---|---|---|---|
| Month Header (Row 1) | Jan | Feb | Mar | Apr |
| Revenue $k (Row 2) | 120 | 85 (< 100 dropped!) | 190 | 140 |
| Filtered & Sorted (>=100) | 1st: Mar (190) | 2nd: Apr (140) | 3rd: Jan (120) | Sum = 450 |
Rank qualifying horizontal month headers high-to-low
CHOOSEROWS(..., 1) extracts the sorted Month header row so TEXTJOIN displays the ranked sequence.
=IF(OR(COUNT(B2:E2)<>4,COUNT(B4)<>1),"Invalid inputs",IFERROR(TEXTJOIN(" > ", TRUE, CHOOSEROWS(SORT(FILTER(B1:E2, B2:E2 >= B4), 2, -1, TRUE), 1)),"None"))Return #1 horizontal column header or sum qualifying columns
The first result returns a month header. The example also checks the sum of qualifying revenue values. IFERROR displays None for an empty result, but also hides other evaluation errors; inspect the source if None is unexpected.
=IF(OR(COUNT(B2:E2)<>4,COUNT(B4)<>1),"Invalid inputs",IFERROR(INDEX(SORT(FILTER(B1:E2, B2:E2 >= B4), 2, -1, TRUE), 1, 1), "None"))Try the example
Use the original values or change them to test your understanding.