Excel functions / Dynamic arrays
CHOOSECOLS function
Returns the columns you name from an array, in the order you name them.
Syntax
=CHOOSECOLS(array, col_num1, [col_num2], …)
Arguments
array: The array to pick columns from.col_num: A column number: 1 is the first, -1 the last.
When to use it
CHOOSECOLS returns the columns you list, by number, in the order you list them. It reorders a table or keeps only the columns a report needs, without copying anything.
Examples
Rep and sales only
A B C D E F 1 Region Rep Sales Rep Sales 2 North Ana 1200 Ana 1200 3 South Ben 800 Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E1:
=CHOOSECOLS(A1:C3, 2, 3)→ Rep, Sales; Ana, 1200; Ben, 800Columns in a new order
A B C D E F 1 Region Rep Sales Sales Region 2 North Ana 1200 1200 North 3 South Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E1:
=CHOOSECOLS(A1:C2, 3, 1)→ Sales, Region; 1200, North
Common mistakes
- A column that is not there. Asking for column 4 of a three-column range gives #VALUE!.
CHOOSECOLS in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: ZVOLITSLOUPCE
- Danish: VÆLGKOL
- Dutch: KIES.KOLOMMEN
- Finnish: VALITSESARAKKEET
- French: CHOISIRCOLS
- German: SPALTENWAHL
- Hungarian: OSZLOPVÁLASZTÁS
- Italian: SCEGLI.COL
- Norwegian: VELGKOL
- Polish: WYBIERZ.KOLUMNY
- Portuguese (Brazil): ESCOLHERCOLS
- Portuguese (Portugal): ESCOLHERCOLS
- Russian: ВЫБОРСТОЛБЦ
- Spanish: ELEGIRCOLS
- Swedish: VÄLJKOL
- Turkish: SÜTUNSEÇ
Try CHOOSECOLS in the playground
Practise it
Related functions
- CHOOSEROWS: Returns the rows you name from an array, in the order you name them.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- INDEX: The value at a given row and column of a range.