How to use the CHOOSECOLS function
The CHOOSECOLS function returns given columns from a cell range or array.
Dynamic array formula in cell B9:
The CHOOSECOLS function is available to Excel 365 users and is in the "Array manipulation" category.
Table of Contents
- 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
- Get Excel file
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
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:
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
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:
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
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:
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
The CHOOSECOLS function lets you use negative numbers, this will make the function count from the right.
Formula in cell B9:
CHOOSECOLS(array, col_num1, [col_num2], …)
7. CHOOSECOLS function returns a #VALUE! 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
The image above shows a formula that returns values in random order from columns two and three.
Dynamic array formula in cell B8:
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
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:
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}
How to use the CHOOSECOLS functionv2
Useful links
CHOOSECOLS function - Microsoft support
CHOOSECOLS Function in Excel: Explained
CHOOSECOLS function in Excel to get columns from array or range
Functions in 'Array manipulation' category
The CHOOSECOLS function function is one of 11 functions in the 'Array manipulation' category.
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