Can CHOOSECOLS return non-adjacent columns in one step?

Yes. Provide each desired column index as a separate argument, regardless of whether the columns are next to each other. CHOOSECOLS will assemble them in the order you specify.

What happens if I omit the optional column arguments?

At least one column index (col_num1) is required. If you only supply the array and no column numbers, Excel returns a #VALUE! error because the function does not know which columns to return.

Is CHOOSECOLS volatile like INDIRECT?

No. CHOOSECOLS recalculates only when its arguments change, so it is not volatile. This makes it more efficient than functions that recalculate on every worksheet change.

How does CHOOSECOLS differ from using INDEX with a column number?

INDEX returns a single cell or a rectangular range when given both row and column arguments, whereas CHOOSECOLS returns an entire set of columns at once without needing to specify row numbers. CHOOSECOLS is therefore simpler for extracting whole columns from a table.

CHOOSECOLS function

CHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

=CHOOSECOLS(array, col_num1, ...)

Generate a CHOOSECOLS formula

Describe what you need. The generator will reach for CHOOSECOLS where CHOOSECOLS is the right tool, and tell you when it is not.

Arguments

How CHOOSECOLS reads its arguments

Argument	Required	Description
array	Required	The source range or literal array; can contain text, numbers, dates, or errors, and must be rectangular.
col_num1	Required	The first column index (1-based) to extract; non-numeric or zero triggers an error.
...	Repeating	Additional column indexes, each 1-based; omitted indexes are ignored, duplicates return duplicate columns.

Returns

It returns a two-dimensional array matching the height of the source and the number of requested columns.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Show address and list price for quick comparison

Address	Beds	Baths	Price	DOM
123 Main St	3	2	350000	12
456 Oak Ave	4	3	420000	5
789 Pine Rd	2	1	275000	30
321 Elm St	3	2	310000	20

=CHOOSECOLS({"Address","Beds","Baths","Price","DOM";"123 Main St",3,2,350000,12;"456 Oak Ave",4,3,420000,5;"789 Pine Rd",2,1,275000,30;"321 Elm St",3,2,310000,20},1,4)

Result: AddressPrice123 Main St350000456 Oak Ave420000789 Pine Rd275000321 Elm St310000

The formula feeds the full listing table as a literal array and asks CHOOSECOLS for column 1 (Address) and column 4 (Price). Because column numbers are 1-based, the function extracts exactly those two columns while keeping the original row order. The result spills into a 5 × 2 block, showing each property's address next to its asking price, which is ideal for a quick side-by-side price review.

2. Create a bedroom-and-bath summary table

Address	Beds	Baths	Price	DOM
123 Main St	3	2	350000	12
456 Oak Ave	4	3	420000	5
789 Pine Rd	2	1	275000	30
321 Elm St	3	2	310000	20

=CHOOSECOLS({"Address","Beds","Baths","Price","DOM";"123 Main St",3,2,350000,12;"456 Oak Ave",4,3,420000,5;"789 Pine Rd",2,1,275000,30;"321 Elm St",3,2,310000,20},2,3)

Result: BedsBaths32432132

Here we need only the number of bedrooms and bathrooms for each listing. By passing column indexes 2 and 3 to CHOOSECOLS, the function discards the address, price, and DOM columns, returning a compact matrix of just those two attributes. This is useful when feeding the data into a chart that compares bedroom-to-bathroom ratios across properties.

3. List days on market for properties priced over $300k

Address	Beds	Baths	Price	DOM
123 Main St	3	2	350000	12
456 Oak Ave	4	3	420000	5
789 Pine Rd	2	1	275000	30
321 Elm St	3	2	310000	20

=CHOOSECOLS(FILTER({"Address","Beds","Baths","Price","DOM";"123 Main St",3,2,350000,12;"456 Oak Ave",4,3,420000,5;"789 Pine Rd",2,1,275000,30;"321 Elm St",3,2,310000,20}, {350000,420000,275000,310000}>300000),5)

Result: DOM12520

First FILTER reduces the original array to rows where the price exceeds $300,000, yielding three qualifying listings. CHOOSECOLS then extracts column 5 (Days on Market) from that filtered result. The final spill shows a single-column list of how long each high-priced home has sat on the market, supporting a quick assessment of market velocity.

Common errors

Which CHOOSECOLS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	A column index argument is text or a non-numeric value, which CHOOSECOLS cannot interpret as a column number.	Replace the text with a numeric index, e.g., use 2 instead of "two".
#REF!	A requested column number exceeds the width of the source array; for example, asking for column 7 when the array has only five columns.	Check the array dimensions and use only column numbers that exist, or wrap the call in IFERROR to supply a fallback.
#SPILL!	The destination area already contains data, preventing the array result from spilling into adjacent cells.	Clear the obstructing cells or place the formula in a location with enough empty space below and to the right.

Tips and when to use something else

Column numbers are 1-based; the first column of the array is 1, not 0.
You can pass duplicate column numbers to repeat a column in the output, useful for side-by-side comparisons.
When you need to pull rows instead of columns, use CHOOSEROWS, which works on the same principle but selects by row index.
Combine CHOOSECOLS with SORT or FILTER to reorder or limit the data before extracting the columns.

Related functions

CHOOSEROWSCHOOSEROWS returns specific rows from a given array based on one or more row numbers, spilling the selected rows into adjacent cells.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.

Frequently asked questions

Can CHOOSECOLS return non-adjacent columns in one step?: Yes. Provide each desired column index as a separate argument, regardless of whether the columns are next to each other. CHOOSECOLS will assemble them in the order you specify.
What happens if I omit the optional column arguments?: At least one column index (col_num1) is required. If you only supply the array and no column numbers, Excel returns a #VALUE! error because the function does not know which columns to return.
Is CHOOSECOLS volatile like INDIRECT?: No. CHOOSECOLS recalculates only when its arguments change, so it is not volatile. This makes it more efficient than functions that recalculate on every worksheet change.
How does CHOOSECOLS differ from using INDEX with a column number?: INDEX returns a single cell or a rectangular range when given both row and column arguments, whereas CHOOSECOLS returns an entire set of columns at once without needing to specify row numbers. CHOOSECOLS is therefore simpler for extracting whole columns from a table.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17