Dynamic arrays
CHOOSEROWS function: pull specific rows or the latest entry from the bottom
Extract milestone rows by position or fetch the last row of a growing log with CHOOSEROWS and negative row indexes.
Compare first and latest periods without rewriting ranges
CHOOSEROWS(array, row_num1, [row_num2], ...) returns specific rows from a range by their 1-based position. Passing -1 always retrieves the bottom row of the range, and CHOOSEROWS(A2:C5, 1, -1) pairs the opening quarter with the closing quarter as two rows for comparison.
| Quarter (A) | Leads (B) | Closed (C) |
|---|---|---|
| Q1 | 140 | 28 |
| Q2 | 165 | 35 |
| Q3 | 150 | 31 |
| Q4 | 190 | 44 |
Read the latest row (-1) or sum selected milestones
CHOOSEROWS(A2:C5, -1) isolates Q4; wrapping with INDEX(..., 1, 3) reads Closed deals (44). Combining CHOOSEROWS(A2:C5, 1, 4) with CHOOSECOLS(..., 3) sums Q1 + Q4 Closed deals (28 + 44 = 72).
=INDEX(CHOOSEROWS(A2:C5, -1), 1, 3)Out-of-bounds row numbers return #VALUE!
Row index 0 or any positive/negative number beyond the row count of the input array returns #VALUE!.
=SUM(CHOOSECOLS(CHOOSEROWS(A2:C5, 1, 4), 3))Try the example
Use the original values or change them to test your understanding.