Search code examples
excelexcel-formulaexcel-2021

Why CHOOSE function doesn't return an dynamic array correctly?

I'm trying to use the CHOOSE function to return just a few non adjacents columns from a table, based on this answer, but the result doesn't look correct.

Simulating the same problem in the table below, I realized that the result is the same.

enter image description here

What could be happening? This formula is not compatible with my version? The FILTER and UNIQUE formulas return a dynamic array correctly.

P.S.: "ESCOLHER" is equivalent function for CHOOSE in my language.

Solution

  • The Separators

    • There are a few separators in Excel. Three (four) are of interest.
    • One is called the list separator, and the other two are called the row and the column separators.
    • This is a US website, so we kind of agree to use their separators: The US list and column separators are a comma (,), while the row separator is a semicolon (;).
    • I still haven't heard that there is a different row separator in any language. So you can always define a single column with e.g. {1;2}. If you need a row, you can use TOROW({1;2}). If you don't have M365, you can use TRANSPOSE({1;2}). These should work in any language.
    • In the formula =HSTACK(B2:B7,E2:E7), the comma is the list separator and it is assumed that OP knows to replace the comma with his list separator, the semicolon.
    • In the expression (array) {1,2} the comma is the column separator. Since OP's Excel uses the comma as a decimal separator, an alternative column separator is needed namely the backslash (\) as he has already discovered.
    • Other languages might use different column separators e.g. a dot (.) as suggested by Mayukh Bhattacharya.
    • Nevertheless, the more important question is: How do you discover your row and column separators?

    The Row Separator (Most Probably a Semicolon)

    • In the range A1:A3 write the numbers 1,2,3. Now in cell B1 write the formula =A1:A3 and press Enter. Now select B1 and press F2 to enter the formula and press F9 to evaluate the formula. The result should be ={1;2;3} (could be a different separator). Press Esc to exit the formula.

    The Column Separator (Default (US) Is a Comma)

    • In the range A1:C1 write the numbers 1,2,3. Now in cell D1 write the formula =A1:C1 and press Enter. Now select D1 and press F2 to enter the formula and press F9 to evaluate the formula. The result will be ={1,2,3} with a possibly different separator (e.g. ., \). Press Esc to exit the formula.