Available in:Excel 2024 or later (Works in Excel 2024, every later version, Microsoft 365 and Excel for the web)Category:Dynamic arrays

CHOOSECOLS

KIES.KOLOMMEN in Dutch Excel

Returns the columns you name by number, in the order you name them, as a block that spills; negative numbers count from the right.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: G2 holds the formula
Row	A	B	C	D	E	G	H	I
1	Entry	Date	Journal	Account	Amount
2	25-0601	3 Nov 2025	INK	45004500	€2,480.00€ 2.480,00	25-0601=CHOOSECOLS(A2:E6, 1, 4, 5)=CHOOSECOLS(A2:E6; 1; 4; 5)	45004500	€2,480.00€ 2.480,00
3	25-0602	5 Nov 2025	VRK	80008000	€6,050.00€ 6.050,00	25-0602	80008000	€6,050.00€ 6.050,00
4	25-0603	12 Nov 2025	BNK	11001100	€9,680.00€ 9.680,00	25-0603	11001100	€9,680.00€ 9.680,00
5	25-0604	18 Nov 2025	MEM	40004000	€15,000.00€ 15.000,00	25-0604	40004000	€15,000.00€ 15.000,00
6	25-0605	24 Nov 2025	INK	45004500	€1,210.00€ 1.210,00	25-0605	45004500	€1,210.00€ 1.210,00

=CHOOSECOLS(A2:E6, 1, 4, 5)

=CHOOSECOLS(A2:E6; 1; 4; 5)

What CHOOSECOLS does and when to use it

What it does: it picks columns out of a range by their number and returns them in the order you name them, as a block that spills into the cells next to the formula, for example entry, account and amount from a journal export.

When to use it: when an export has more columns than your workpaper needs, or has them in the wrong order, and the result must follow the source. Combine it with XMATCH when column positions can change between exports.

Three main gains:

Select and reorder in one step: the columns come back in the order of your numbers.
Follows the source: paste a new export and the result updates, with no columns to delete or drag.
Counts from the right: -1 is the last column, so a closing column needs no counting.

How Excel evaluates it

Excel reads array and counts its columns. It then walks through your column numbers from left to right. For each number it checks that it is not 0 and not larger than the number of columns; a negative number is turned into a position from the right, so -1 becomes the last column. It copies that whole column, all rows, into the next column of the result.

If one number is invalid the whole result is #VALUE!. The finished block spills from the formula cell, as tall as array and as wide as the number of column numbers you gave.

Watch out: Fixed column numbers such as 1, 4, 6 point at positions, not at names: when next year's export has an extra column, the workpaper silently shows the wrong columns, without any error.

Mental model

Picking cards from a hand and laying them out in a new row: you decide which ones and in what order, and the hand itself stays untouched.

Terms used on this page: spill, array, range, cell

Syntax

Official CHOOSECOLS documentation

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

CHOOSECOLS(array; col_num1; [col_num2, ...]; ...)

Arguments
Argument	Type	Meaning
`array`	Required	The range or array that holds the columns you pick from.
`col_num1`	Required	Number of the first column to return; negative counts from the right.
`col_num2, ...`	Optional	Further column numbers; each one adds a column to the result.

Return value:An array with the chosen columns of array, in the order of the column numbers

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 19 KB)

Beginner

Pull entry, account and amount from a journal export

You received the XAF auditfile of Veldhuis Installatie BV as a sheet with five columns. For your workpaper you only need the entry number, the account and the amount, next to each other. One formula collects those three columns and leaves the export untouched.

Sheet excerpt: G2 holds the formula
Row	A	B	C	D	E	G	H	I
1	Entry	Date	Journal	Account	Amount
2	25-0601	3 Nov 2025	INK	45004500	€2,480.00€ 2.480,00	25-0601=CHOOSECOLS(A2:E6, 1, 4, 5)=CHOOSECOLS(A2:E6; 1; 4; 5)	45004500	€2,480.00€ 2.480,00
3	25-0602	5 Nov 2025	VRK	80008000	€6,050.00€ 6.050,00	25-0602	80008000	€6,050.00€ 6.050,00
4	25-0603	12 Nov 2025	BNK	11001100	€9,680.00€ 9.680,00	25-0603	11001100	€9,680.00€ 9.680,00
5	25-0604	18 Nov 2025	MEM	40004000	€15,000.00€ 15.000,00	25-0604	40004000	€15,000.00€ 15.000,00
6	25-0605	24 Nov 2025	INK	45004500	€1,210.00€ 1.210,00	25-0605	45004500	€1,210.00€ 1.210,00
7

Formula cell (G2)Spilled result

Formula in G2

=CHOOSECOLS(A2:E6, 1, 4, 5)

=CHOOSECOLS(A2:E6; 1; 4; 5)

Result: 25-0601 | 4500 | €2,480.00 / 25-0602 | 8000 | €6,050.00 / 25-0603 | 1100 | €9,680.00 / 25-0604 | 4000 | €15,000.00 / 25-0605 | 4500 | €1,210.0025-0601 | 4500 | € 2.480,00 / 25-0602 | 8000 | € 6.050,00 / 25-0603 | 1100 | € 9.680,00 / 25-0604 | 4000 | € 15.000,00 / 25-0605 | 4500 | € 1.210,00

Step by step

Excel reads array, A2:E6: 5 rows and 5 columns. The columns are numbered 1 to 5: Entry is 1, Date 2, Journal 3, Account 4, Amount 5.
The first column number is 1, so column A goes into the first column of the result: 25-0601, 25-0602, 25-0603, 25-0604, 25-0605.
The second number is 4, so column D, the account, goes in next: 4500, 8000, 1100, 4000, 4500. The third number is 5, so column E, the amount, closes the block: 2,4802.480, 6,0506.050, 9,6809.680, 15,00015.000, 1,2101.210.
The result is 5 rows by 3 columns and spills from G2 to I6. Date and journal (columns B and C) are not in it.
Change the formula to =CHOOSECOLS(A2:E6, 5, 1): the block is now 2 columns wide, with the amount first and the entry second. The order of your numbers is the order of the columns.

Watch out: The positions are counted inside A2:E6, not in the sheet. If you pass B2:F6 instead, number 1 now means column B. A number above 5 gives #VALUE!.

More on this example: why it works, 1 variation

Why it works

Every column number is a position in array, counted from the left starting at 1. Excel does not look at the headers, so it works the same with any content. All rows come along, because CHOOSECOLS only decides which columns to keep.

Variations

=CHOOSECOLS(A2:E6, 4, 4, 5)

=CHOOSECOLS(A2:E6; 4; 4; 5)

Repeat a number to get a column twice, for example when you want the account next to itself for a lookup key.

Intermediate

Pick the relation, then the overdue days and amount, counting from the right

From the receivables aging list of Veldhuis Installatie BV you need, per customer, the days overdue and the open amount, in that order, as the start of the confirmation list. Both columns are always the last two of the file, and the client sometimes adds columns in front, so you count from the right with negative numbers. One invoice has no due date, so its days overdue are blank.

Sheet excerpt: G2 holds the formula
Row	A	B	C	D	E	G	H	I
1	Relation	Invoice	Due date	Open amount	Days overdue
2	D1001	25-1201	28 Aug 2025	€4,235.00€ 4.235,00	125125	D1001=CHOOSECOLS(A2:E7, 1, -1, -2)=CHOOSECOLS(A2:E7; 1; -1; -2)	125125	€4,235.00€ 4.235,00
3	D1002	25-1202	2 Oct 2025	€12,100.00€ 12.100,00	9090	D1002	9090	€12,100.00€ 12.100,00
4	D1003	25-1203	26 Nov 2025	€2,420.00€ 2.420,00	3535	D1003	3535	€2,420.00€ 2.420,00
5	D1004	25-1204		€7,865.00€ 7.865,00		D1004	00	€7,865.00€ 7.865,00
6	D1005	25-1205	15 Dec 2025	€1,815.00€ 1.815,00	1616	D1005	1616	€1,815.00€ 1.815,00
7	D1006	25-1206	30 Jun 2025	€9,680.00€ 9.680,00	184184	D1006	184184	€9,680.00€ 9.680,00
8

Formula cell (G2)Spilled resultNote: Days overdue are counted at 31 December 2025.

Formula in G2

=CHOOSECOLS(A2:E7, 1, -1, -2)

=CHOOSECOLS(A2:E7; 1; -1; -2)

Result: D1001 | 125 | €4,235.00 / D1002 | 90 | €12,100.00 / D1003 | 35 | €2,420.00 / D1004 | 0 | €7,865.00 / D1005 | 16 | €1,815.00 / D1006 | 184 | €9,680.00D1001 | 125 | € 4.235,00 / D1002 | 90 | € 12.100,00 / D1003 | 35 | € 2.420,00 / D1004 | 0 | € 7.865,00 / D1005 | 16 | € 1.815,00 / D1006 | 184 | € 9.680,00

Step by step

Excel reads array, A2:E7: 6 rows, 5 columns. Counted from the right, -1 is column 5 (Days overdue) and -2 is column 4 (Open amount).
The first number, 1, puts column A (the relation) first: D1001, D1002, D1003, D1004, D1005, D1006.
The second number, -1, adds column E: 125, 90, 35, blank, 16, 184. The blank cell E5 comes through as 0, because an array cannot hold an empty cell.
The third number, -2, adds column D: 4,2354.235, 12,10012.100, 2,4202.420, 7,8657.865, 1,8151.815, 9,6809.680.
The result is 6 rows by 3 columns and spills from G2 to I7. Invoice D1004 shows 0 days overdue and 7,8657.865, although the true answer is unknown, because its due date is missing.
Suppose the client inserts a column between B and C next year. The range becomes A2:F7, and -1 and -2 still point at the last two columns. With fixed numbers 5 and 4 you would now get the wrong columns without an error.

Watch out: The blank in E5 shows as 0 and looks like a current invoice. It is a missing due date that you still have to follow up with the client.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

A negative number is a position counted from the right edge of array, so it stays correct as long as the columns you need stay at the end. A positive and a negative number can be mixed freely in one call, and each of them gives one column in the order you wrote them.

More to watch out for

Negative indexes count from the right, so if the client adds a column between the amount and the days overdue (or at the far right) instead of in front, -2 or -1 suddenly points at the wrong column and still gives no error. Check the headers of the result against the source.

Variations

=CHOOSECOLS(A2:E7, 1, 5, 4)

=CHOOSECOLS(A2:E7; 1; 5; 4)

The same result with positive numbers. Prefer this when the layout of the file is fixed, because 5 and 4 read faster than -1 and -2.

Advanced

Pick purchase columns by header name with XMATCH

For the VAT recalculation at Veldhuis Installatie BV you take purchase postings from the auditfile and only need the entry, the VAT code and the amount including VAT. Last year the export had the columns in another order, so you do not want to type column numbers. The names of the columns you want sit in I1:K1, and the formula looks up their positions in the header row.

Sheet excerpt: H3 holds the formula
Row	A	B	C	D	E	F	H	I	J	K
1	Entry	Date	Journal	Account	VAT code	Amount	Columns	Entry	VAT code	Amount
2	25-0701	6 Oct 2025	INK	45004500	V21	€2,420.00€ 2.420,00
3	25-0702	14 Oct 2025	INK	40004000	V9	€1,090.00€ 1.090,00	25-0701=CHOOSECOLS(A2:F7, XMATCH(I1:K1, A1:F1))=CHOOSECOLS(A2:F7; XMATCH(I1:K1; A1:F1))	V21	€2,420.00€ 2.420,00
4	25-0703	21 Oct 2025	INK	45004500	V21	€6,050.00€ 6.050,00	25-0702	V9	€1,090.00€ 1.090,00
5	25-0704	4 Nov 2025	INK	45004500	V0	€880.00€ 880,00	25-0703	V21	€6,050.00€ 6.050,00
6	25-0705	18 Nov 2025	INK	40004000	V21	€3,267.00€ 3.267,00	25-0704	V0	€880.00€ 880,00
7	25-0706	2 Dec 2025	INK	45004500	V9	€545.00€ 545,00	25-0705	V21	€3,267.00€ 3.267,00
8							25-0706	V9	€545.00€ 545,00
9

Formula cell (H3)InputSpilled result

Formula in H3

=CHOOSECOLS(A2:F7, XMATCH(I1:K1, A1:F1))

=CHOOSECOLS(A2:F7; XMATCH(I1:K1; A1:F1))

Result: 25-0701 | V21 | €2,420.00 / 25-0702 | V9 | €1,090.00 / 25-0703 | V21 | €6,050.00 / 25-0704 | V0 | €880.00 / 25-0705 | V21 | €3,267.00 / 25-0706 | V9 | €545.0025-0701 | V21 | € 2.420,00 / 25-0702 | V9 | € 1.090,00 / 25-0703 | V21 | € 6.050,00 / 25-0704 | V0 | € 880,00 / 25-0705 | V21 | € 3.267,00 / 25-0706 | V9 | € 545,00

Step by step

Excel starts with the inner function, XMATCH(I1:K1, A1:F1). It looks up each of the three names in the header row A1:F1.
Entry is the 1st header, VAT code is the 5th and Amount is the 6th. XMATCH returns the array {1,5,6}, one position per name.
CHOOSECOLS(A2:F7, {1,5,6}) now takes column 1 (entries), column 5 (VAT codes) and column 6 (amounts) from the six data rows.
The block is 6 rows by 3 columns and spills from H3 to J8. Row 1 is 25-0701, V21, 2,4202.420; row 4 is 25-0704, V0, 880; row 6 is 25-0706, V9, 545.
Type Account in K1 instead of Amount. XMATCH now returns {1,5,4}, and the third column of the block shows 4500, 4000, 4500, 4500, 4000, 4500. Only the names changed, not the formula.
Type a name that is not in the headers, such as Amounts. XMATCH returns #N/A for it and the formula returns #N/A instead of the block. The error tells you at once that the name is wrong.

Watch out: Typing the numbers 1, 5, 6 by hand works today but returns the wrong columns without any error once the client inserts a column. The header lookup removes that risk.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

XMATCH turns names into positions, and CHOOSECOLS turns positions into columns, so the link between your workpaper and the export is the header text instead of a fixed place. If the client moves a column, the positions change and the result stays correct.

More to watch out for

A header with a trailing space, such as 'Amount ' in the export, is not found by XMATCH. You see #N/A, and the fix is to correct the name in I1:K1 or clean the header with TRIM.

Variations

=CHOOSECOLS(A2:F7, 1, 5, 6)

=CHOOSECOLS(A2:F7; 1; 5; 6)

Fixed numbers: shorter and fine for a one-off check on a file you have in front of you, but it does not survive a change in the layout.

=HSTACK(A2:A7, E2:F7)

=HSTACK(A2:A7; E2:F7)

Joining separate ranges with HSTACK gives the same block here, but it needs one reference per run of columns and does not follow a header name.

Master

Trial balance extract by header name, only balances above a threshold

You receive the trial balance (saldibalans) of Veldhuis Installatie BV as a sheet with six columns. Your workpaper needs account, name and closing balance, and only for accounts at or above the reporting threshold in I2, debit or credit.

The column names are in I1:K1 and the column to test is in I3, so the next reviewer can change them. The formula must name its parts and say clearly when nothing qualifies.

Sheet excerpt: H5 holds the formula
Row	A	B	C	D	E	F	H	I	J	K
1	Account	Name	Opening	Debit	Credit	Closing	Columns	Account	Name	Closing
2	11001100	Bank	€52,000.00€ 52.000,00	€310,000.00€ 310.000,00	€295,000.00€ 295.000,00	€67,000.00€ 67.000,00	Reporting threshold	500500
3	13001300	Receivables	€84,000.00€ 84.000,00	€520,000.00€ 520.000,00	€498,000.00€ 498.000,00	€106,000.00€ 106.000,00	Test on	Closing
4	16001600	Payables	-€61,000.00€ -61.000,00	€205,000.00€ 205.000,00	€214,500.00€ 214.500,00	-€70,500.00€ -70.500,00
5	18001800	VAT payable	-€9,500.00€ -9.500,00	€41,000.00€ 41.000,00	€43,200.00€ 43.200,00	-€11,700.00€ -11.700,00	11001100=LET( heads, A1:F1, data, A2:F9, view, CHOOSECOLS(data, XMATCH(I1:K1, heads)), size, ABS(CHOOSECOLS(data, XMATCH(I3, heads))), keep, size >= I2, IF(SUM(keep * 1) = 0, "No balances at or above the threshold", FILTER(view, keep)) )=LET( heads; A1:F1; data; A2:F9; view; CHOOSECOLS(data; XMATCH(I1:K1; heads)); size; ABS(CHOOSECOLS(data; XMATCH(I3; heads))); keep; size >= I2; IF(SUM(keep * 1) = 0; "No balances at or above the threshold"; FILTER(view; keep)) )	Bank	€67,000.00€ 67.000,00
6	40004000	Payroll costs	€0.00€ 0,00	€338,470.00€ 338.470,00	€0.00€ 0,00	€338,470.00€ 338.470,00	13001300	Receivables	€106,000.00€ 106.000,00
7	45004500	Housing costs	€0.00€ 0,00	€420.00€ 420,00	€0.00€ 0,00	€420.00€ 420,00	16001600	Payables	-€70,500.00€ -70.500,00
8	49504950	Office expenses	€0.00€ 0,00	€310.00€ 310,00	€0.00€ 0,00	€310.00€ 310,00	18001800	VAT payable	-€11,700.00€ -11.700,00
9	80008000	Revenue	€0.00€ 0,00	€0.00€ 0,00	€430,000.00€ 430.000,00	-€430,000.00€ -430.000,00	40004000	Payroll costs	€338,470.00€ 338.470,00
10							80008000	Revenue	-€430,000.00€ -430.000,00
11

Formula cell (H5)InputSpilled result

Formula in H5

=LET(
  heads, A1:F1,
  data, A2:F9,
  view, CHOOSECOLS(data, XMATCH(I1:K1, heads)),
  size, ABS(CHOOSECOLS(data, XMATCH(I3, heads))),
  keep, size >= I2,
  IF(SUM(keep * 1) = 0, "No balances at or above the threshold", FILTER(view, keep))
)

=LET(
  heads; A1:F1;
  data; A2:F9;
  view; CHOOSECOLS(data; XMATCH(I1:K1; heads));
  size; ABS(CHOOSECOLS(data; XMATCH(I3; heads)));
  keep; size >= I2;
  IF(SUM(keep * 1) = 0; "No balances at or above the threshold"; FILTER(view; keep))
)

Result: 1100 | Bank | €67,000.00 / 1300 | Receivables | €106,000.00 / 1600 | Payables | -€70,500.00 / 1800 | VAT payable | -€11,700.00 / 4000 | Payroll costs | €338,470.00 / 8000 | Revenue | -€430,000.001100 | Bank | € 67.000,00 / 1300 | Receivables | € 106.000,00 / 1600 | Payables | € -70.500,00 / 1800 | VAT payable | € -11.700,00 / 4000 | Payroll costs | € 338.470,00 / 8000 | Revenue | € -430.000,00

Step by step

LET names the first two parts: heads is the header row A1:F1 and data is the body A2:F9, eight accounts. Nothing is calculated yet.
view: XMATCH(I1:K1, heads) looks up Account, Name and Closing in the headers and returns {1,2,6}. CHOOSECOLS(data, {1,2,6}) gives an 8 by 3 block: account, name and closing balance of all eight accounts.
size: I3 holds Closing, XMATCH returns 6, and CHOOSECOLS(data, 6) is the closing column: 67,00067.000; 106,000106.000; -70,50070.500; -11,70011.700; 338,470338.470; 420; 310; -430,000430.000. ABS removes the signs: 67,00067.000; 106,000106.000; 70,50070.500; 11,70011.700; 338,470338.470; 420; 310; 430,000430.000.
keep compares each size with I2, 500: 1, 1, 1, 1, 1, 0, 0, 1. Housing costs (420) and office expenses (310) fall below the threshold.
SUM(keep * 1) is 6, not 0, so IF takes the FILTER branch. FILTER(view, keep) keeps rows 1, 2, 3, 4, 5 and 8 of the 8 by 3 block.
The result spills from H5 to J10: 1100, Bank, 67,00067.000 down to 8000, Revenue, -430,000430.000. Set I2 to 500000 and every size is below it, SUM(keep * 1) is 0, and H5 shows No balances at or above the threshold.

Watch out: The mirror-image mistake is to leave out ABS: size >= I2 then drops every credit balance, because -70,50070.500, -11,70011.700 and -430,000430.000 are all below 500. Payables, VAT payable and revenue vanish from the list and the list still looks tidy.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

CHOOSECOLS is used twice on the same data with different jobs: once to build the columns you show, once to extract the column you test. Because the test column is not part of the output, the filter can use it without displaying it.

Do not use this when the layout of the file never changes and you need only a few columns: =CHOOSECOLS(A2:F9, 1, 2, 6) is simpler to review, and in Excel 2021 or earlier it does not work at all. A static alternative is Power Query, which also selects columns by name and survives layout changes, at the cost of refreshing by hand when the source changes.

More to watch out for

If the name in I3 does not match any header exactly, XMATCH returns #N/A, size is an error and the whole formula shows #N/A. Because the IF only tests SUM(keep * 1), the error is not hidden by the message text.
Pointing data at whole columns such as A:F makes Excel test about a million rows on every recalculation, and the empty rows count as balances of 0. Use the exact range or a Table.

Variations

=FILTER(CHOOSECOLS(A2:F9, 1, 2, 6), ABS(F2:F9) >= I2, "No balances at or above the threshold")

=FILTER(CHOOSECOLS(A2:F9; 1; 2; 6); ABS(F2:F9) >= I2; "No balances at or above the threshold")

Without LET and without header lookup: shorter, and if_empty is safe here because nothing is nested around it. Prefer it when the layout is fixed.

Common mistakes

Common mistakes
Symptom	Cause	Fix
#VALUE! with no obvious cause	The formula returns #VALUE! and you cannot see why.	One of the column numbers is 0 or larger than the width of array, for example 6 for a five-column range. Count the columns of the range you pass, not of the sheet.
0 where the source cell is empty	An empty cell in a chosen column shows up as 0 in the result.	Excel turns blanks in an array into 0. Format the column so zeros stay readable, or test the source column with ISBLANK before you read a 0 as a real figure.
Wrong columns after the export changed	The column numbers are typed in as fixed numbers and the layout of the export changes.	Look the positions up with XMATCH on the header row, or use negative numbers when the column you need is always the last one.
#SPILL! although the formula is correct	The result is #SPILL! although the formula is correct.	Something already sits in the cells the block needs. Click the error, choose Select Obstructing Cells and clear them.