Excel CHOOSECOLS: Pick Columns From an Array
By Szabó Gergő · Updated
CHOOSECOLS returns only the column indexes you list, in that order. Use it to slim a FILTER result.
Syntax and arguments
=CHOOSECOLS(array, col_num1, [col_num2], ...)- array
- A range or spilled array.
- col_num1
- 1 is the first column of that array, not worksheet column A.
CHOOSECOLS examples
01
Name and amount
FILTER returns A:E.
=CHOOSECOLS(FILTER(A2:E100,C2:C100="Paid"),1,4)Keeps the first and fourth columns of the filtered array.
02
Reorder
Want amount first.
=CHOOSECOLS(A2:C20,3,1)Column C then column A.
03
Drop a middle field
VSTACK of two tables.
=CHOOSECOLS(VSTACK(A2:D20,F2:I15),1,2,4)Skips the third stacked column.
Common mistakes
Using worksheet column letters
Indexes are relative to the array. If the array is C:E, 1 is column C.
Index larger than the array width
That returns #VALUE!.
Using it in Excel 2016
CHOOSECOLS needs Microsoft 365.
CHOOSECOLS FAQ
CHOOSECOLS vs INDEX?
INDEX can return one column. CHOOSECOLS can return several in one spill.
Is there CHOOSEROWS?
Yes. It picks rows by position from an array.
Can I use negative indexes?
Yes. -1 is the last column of the array.