CHOOSECOLS function: extract, reorder, and pull last columns dynamically

Build lean client views without deleting middle columns

Pricing sheets often store internal cost and margin columns between the item name and the customer-facing list price. CHOOSECOLS(array, col_num1, [col_num2], ...) extracts only the requested 1-based column positions into a live dynamic array.

You can reorder columns on the fly—for example CHOOSECOLS(A2:D4, 4, 1) puts Price before Item—or pass negative numbers like -1 to grab the rightmost column even if new intermediate columns are inserted later.

Read specific columns or sum the trailing column (-1)

CHOOSECOLS(A2:D4, 1, 4) produces a 3x2 table of [Item, ListPrice]. Wrapping with INDEX(..., 2, 2) reads row 2, column 2 (Chair price = 158) in a single cell, while SUM(CHOOSECOLS(A2:D4, -1)) totals the rightmost column (519).

=INDEX(CHOOSECOLS(A2:D4, 1, 4), 2, 2)

Invalid column index boundaries (#VALUE!)

Column numbers are 1-based from the left (1..N) or -1..-N from the right. Passing 0 or an index whose magnitude exceeds the array width returns #VALUE!.

=SUM(CHOOSECOLS(A2:D4, -1))

Further reading

• Microsoft Support — CHOOSECOLS function