How to use the CHOOSECOLS function

Author: Oscar Cronquist Article last updated on September 29, 2022

CHOOSECOLS function example

The CHOOSECOLS function returns given columns from a cell range or array.

Dynamic array formula in cell B9:

=CHOOSECOLS(B2:D5, 2, 3)

The CHOOSECOLS function is available to Excel 365 users and is in the "Array manipulation" category.

CHOOSECOLS Function Syntax
CHOOSECOLS Function Arguments
CHOOSECOLS Function example
How to combine specific cell range columns - Excel 365
How to combine specific columns based on a given value - Excel 365
How to combine columns using negative values - Excel 365
CHOOSECOLS function returns a #VALUE! error
Extract values from given columns and return the list in random order - Excel 365
Join multiple non-adjacent cell ranges and return given columns (3D range) - Excel 365

1. CHOOSECOLS Function Syntax

CHOOSECOLS(array, col_num1, [col_num2], …)

2. CHOOSECOLS Function Arguments

array	Required. The source cell range or array.
col_num1	Required. A number represents the column in a given cell range.
[col_num2]	Optional. Extra column numbers to be extracted.

3. CHOOSECOLS Function example

CHOOSECOLS function example1

The picture above shows how the CHOOSECOLS function extracts the second and third column from cell range B2:D5.

Dynamic array formula in cell B9:

=CHOOSECOLS(B2:D5, 2, 3)

Note! If any of the values in the source range change, the values in the CHOOSECOLS function output change as well.

3.1 Explaining formula

Step 1 - CHOOSECOLS function

CHOOSECOLS(array, col_num1, [col_num2], …)

Step 2 - Populate arguments

array - B2:D5
col_num1 - 2
[col_num2] - 3

Step 3 - Evaluate function

CHOOSECOLS(B2:D5, 2, 3)

becomes

CHOOSECOLS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2, 3)

and returns

{27, 26; 84, 98;
92, 62; 63, 45}

4. How to combine specific cell range columns - CHOOSECOLS Function

CHOOSECOLS function array

The CHOOSECOLS function lets you use an array in the arguments, the image above shows an array in the second argument.

Dynamic array formula in cell B9:

=CHOOSECOLS(B2:D5, {1; 3})

Explaining formula

Step 1 - Populate the array

The curly brackets let you build an array that you can use in most but not all Excel functions. The ; semicolon is a delimiting character that separates values row by row.

The CHOOSE function requires numbers separated with semicolons or whatever row delimiting character you use.

{1; 3}

Step 2 - CHOOSECOLS function

CHOOSECOLS(array, col_num1, [col_num2], …)

CHOOSECOLS(B2:D5, {1; 3})

becomes

CHOOSECOLS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 3})

and returns

{89, 26;
68, 98;
19, 62;
37,45}

5. How to combine specific columns based on a given value - CHOOSECOLS Function

CHOOSECOLS function text to array

The section shows how to return specific rows based on a string containing numbers. The TEXTSPLIT function separates values into an array.

Dynamic array formula in cell B9:

=CHOOSECOLS(B2:D5, TEXTSPLIT(F2, , ",")*1)

Explaining formula

Step 1 - Split text

The TEXTSPLIT function lets you split a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(F2,,",")

becomes

TEXTSPLIT("1,2", , ",")

and returns {"1"; "2"}.

Note the double quotes, Excel handles these values as text values. The next step converts text values to numbers.

Step 2 - Convert text to numbers

The asterisk lets you multiply numbers in an Excel formula, I am using it here to convert text to numbers.

TEXTSPLIT(F2,,",")*1

becomes

{"1"; "2"}*1

and returns {1; 2}.

Step 3 - CHOOSECOLS function

CHOOSECOLS(B2:D5,TEXTSPLIT(F2,,",")*1)

becomes

CHOOSECOLS(B2:D5, {1; 2})

becomes

CHOOSECOLS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, {1; 2})

and returns

{89, 27;
68, 84;
19, 92;
37, 63}

6. How to combine columns using negative values - CHOOSECOLS Function

CHOOSECOLS function negative values

The CHOOSECOLS function lets you use negative numbers, this will make the function count from the right.

Formula in cell B9:

=CHOOSECOLS(B2:D5, -2, -1)

CHOOSECOLS(array, col_num1, [col_num2], …)

7. CHOOSECOLS function returns a #VALUE! error

CHOOSECOLS function error

The CHOOSECOLS function returns a #VALUE! error when no values can be displayed. There are only three columns in cell range B2:D5.

8. Extract values from given columns and return the list in random order - CHOOSECOLS function

CHOOSECOLS function in random order 1

The image above shows a formula that returns values in random order from columns two and three.

Dynamic array formula in cell B8:

=LET(z, CHOOSECOLS(B2:D5, 2, 3), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

8.1 Explaining formula in cell B8

Step 1 - Filter columns 2 and 3

CHOOSECOLS(B2:D5, 2, 3)

becomes

CHOOSECOLS({89, 27, 26;
68, 84, 98;
19, 92, 62;
37, 63, 45}, 2, 3)

and returns

{27,26;
84,98;
92,62;
63,45}

Step 2 - Rearrange values to a single row

The TOROW function rearranges values from a 2D cell range or array to a single row.

TOROW(array, [ignore], [scan_by_col])

TOROW(CHOOSECOLS(B2:D5,2,3))

becomes

TOROW({27,26;
84,98;
92,62;
63,45}

and returns

{27, 26, 84, 98, 92, 62, 63, 45}.

Step 3 - Count cells

The COLUMNS function returns the number of columns in a given cell range or array.

COLUMNS(array)

COLUMNS(TOROW(CHOOSECOLS(B2:D5,2, 3)))

becomes

COLUMNS({27, 26, 84, 98, 92, 62, 63, 45})

and returns 8.

Step 4 - Count rows

The ROWS function returns the number of rows in a given cell range or array.

ROWS (array)

ROWS(CHOOSECOLS(B2:D5,2, 3))

becomes

ROWS({27,26;
84,98;
92,62;
63,45})

and returns 4.

Step 5 - Create random decimal numbers

The RANDARRAY function returns a table of random numbers across rows and columns.

RANDARRAY([rows], [columns], [min], [max], [whole_number])

RANDARRAY(,COLUMNS(TOROW(CHOOSECOLS(B2:D5,2, 3))))

becomes

RANDARRAY(,6)

and returns

{0.215398134613085, 0.390607168196479, ... ,0.83231474462401}.

Step 6 - Rearrange values in random order

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(TOROW(CHOOSECOLS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(CHOOSECOLS(B2:D5,2, 3))))

becomes

SORTBY({27,26;
84,98;
92,62;
63,45}, {0.215398134613085, 0.390607168196479, ... ,0.83231474462401})

and returns

{84, 98, 27, 92, 62, 26, 45, 63}

Step 7 - Rearrange values to the original array size

The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPCOLS(vector, wrap_count, [pad_with])

WRAPCOLS(SORTBY(TOROW(CHOOSECOLS(B2:D5,2, 4)),RANDARRAY(,COLUMNS(TOROW(CHOOSECOLS(B2:D5,2, 4))))),ROWS(CHOOSECOLS(B2:D5,2, 3)))

becomes

WRAPCOLS({63, 37, 45, 98, 84, 68}, ROWS(CHOOSECOLS(B2:D5,2, 3)))

becomes

WRAPCOLS({84, 98, 27, 92, 62, 26, 45, 63}, 4)

and returns

{84, 98; 27, 92; 62, 26; 45, 63}.

Step 8 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

WRAPCOLS(SORTBY(TOROW(CHOOSECOLS(B2:D5, 2, 3)), RANDARRAY(, COLUMNS(TOROW(CHOOSECOLS(B2:D5, 2, 3))))), TOROW(CHOOSECOLS(B2:D5, 2, 3)))

becomes

LET(z, CHOOSECOLS(B2:D5, 2, 3), x, TOROW(z), WRAPCOLS(SORTBY(x, RANDARRAY(, COLUMNS(x))), ROWS(z)))

9. Join multiple non-adjacent cell ranges and return given columns (3D range) - CHOOSECOLS function

CHOOSECOLS function from multiple sources

The picture above shows a formula that merges three non-contiguous cell ranges and shows rows 1, 5, and 9.

Dynamic array formula in cell B9:

=CHOOSECOLS(VSTACK(B3:D6, F3:H6, J3:L6), 1, 2)

9.1 Explaining formula

Step 1 - Stack values horizontally

The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

VSTACK(array1, [array2],...)

VSTACK(B3:D6, F3:H6, J3:L6)

becomes

VSTACK({"Peach", 43, 1.03;"Blueberry", 39, 1.48;"Apple", 46, 1.1;"Grapefruit", 14, 0.72}, {"Mandarin", 29, 0.78;"Raspberry", 33, 1.07;"Plum", 25, 0.9;"Mango", 37, 1.13}, {"Pear", 17, 0.63;"Orange", 31, 1.06;"Lime", 17, 1.27;"Kiwi", 45, 0.58})

and returns

{"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}

Step 2 - Filter given rows

CHOOSECOLS(VSTACK(B3:D6, F3:H6, J3:L6),1, 2)

becomes

CHOOSECOLS({"Peach", 43, 1.03;
"Blueberry", 39, 1.48;
"Apple", 46, 1.1;
"Grapefruit", 14, 0.72;
"Mandarin", 29, 0.78;
"Raspberry", 33, 1.07;
"Plum", 25, 0.9;
"Mango", 37, 1.13;
"Pear", 17, 0.63;
"Orange", 31, 1.06;
"Lime", 17, 1.27;
"Kiwi", 45, 0.58}, 1, 5, 9)

and returns

{"Peach", 43;
"Blueberry", 39;
"Apple", 46;
"Grapefruit", 14;
"Mandarin", 29;
"Raspberry", 33;
"Plum", 25;
"Mango", 37;
"Pear", 17;
"Orange", 31;
"Lime", 17;
"Kiwi", 45}

More examples

Functions in this article

CHOOSECOLS TEXTSPLIT LET TOROW WRAPCOLS SORTBY RANDARRAY COLUMNS ROWS VSTACK

Functions in 'Array manipulation' category

The CHOOSECOLS function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the CHOOSECOLS function

Table of Contents

1. CHOOSECOLS Function Syntax

2. CHOOSECOLS Function Arguments

3. CHOOSECOLS Function example

3.1 Explaining formula

Step 1 - CHOOSECOLS function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. How to combine specific cell range columns - CHOOSECOLS Function

Explaining formula

Step 1 - Populate the array

Step 2 - CHOOSECOLS function

5. How to combine specific columns based on a given value - CHOOSECOLS Function

Explaining formula

Step 1 - Split text

Step 2 - Convert text to numbers

Step 3 - CHOOSECOLS function

6. How to combine columns using negative values - CHOOSECOLS Function

7. CHOOSECOLS function returns a #VALUE! error

8. Extract values from given columns and return the list in random order - CHOOSECOLS function

8.1 Explaining formula in cell B8

Step 1 - Filter columns 2 and 3

Step 2 - Rearrange values to a single row

Step 3 - Count cells

Step 4 - Count rows

Step 5 - Create random decimal numbers

Step 6 - Rearrange values in random order

Step 7 - Rearrange values to the original array size

Step 8 - Shorten formula

9. Join multiple non-adjacent cell ranges and return given columns (3D range) - CHOOSECOLS function

9.1 Explaining formula

Step 1 - Stack values horizontally

Step 2 - Filter given rows

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment