CHOOSE() function with arrays in Excel

 Jan 30, 2015

The =CHOOSE function in Microsoft Excel can be a confusing, but today, I’ll provide a detailed explanation about this very handy function. In general =CHOOSE(index_num, value1, [value2], ...)  picks value1 if index-num is 1 and picks value2 if index-num is 2 and so on. value1, value2, etc…, could be range values as well for example, you might do this:

=SUM(CHOOSE(A1,A3:C9,E3:J9,M3:Q9))

Here if the value of A1 is 1 then it will be equivalent to =SUM(A3:C9) and if the value of A1 is 2 then it will be =SUM(E3:J9) and so on.

But when CHOOSE() is used with arrays it behaves in a special way. Let’s look at it.

Let’s type:

=CHOOSE({1,2},B:B,L:L)

This command will evaluate for both possibilities of 1 and 2 and so creates a table of two columns. The first column will be B:B and the second one L:L. So you can see this will be useful if you want to put together two separate columns and create a temporary table to do operations on, for instance for a VLOOKUP() operation.

But what if you type:

=CHOOSE({1,2},B2:C6,L2:M6)

How is it going to construct the table? Well the table in this situation the table will be B2:B6 for the first column, and M2:M6 for the second one.

If you type:

=CHOOSE({1,2,3},B2:C6,L2:M6,P2:R6)

The table will have 3 column; B2:B6 for the first column, M2:M6 for the second column and R2:R6 for third one. Note that P2:R6 has 3 column. So if you haven’t found the pattern by now this is how it works; It picks the first range say B2:C6 and tries to put it next to the second range L2:M6 but how is it possible to accommodate 2 tables each having 2 columns into a two column table? Well it picks the first column from the first table, the second column from the second table, the third column from the third table, and so on.

But what if you wrote:

=CHOOSE({2,3},B2:C6,L2:M6,P2:R6) instead of {1,2,3}

In this case because you started the array with 2, it picks the first column from the 2nd range i.e. L2:L6 and the second column from the 3rd range i.e. Q2:Q6.

If you have specified ranges having different rows it goes with the highest number of rows so:

=CHOOSE({1,2},B2:C6,L2:M8)

This will produce B2:B8 for the first column and M2:M8 for the second one. So now let’s have a useful working example. Remember, you almost never use CHOOSE() on its own. It is usually used in other functions. Imagine a table is split up into two separate tables and they are now in two different sheets. You still want to look up a value using VLookup() but unfortunately there is no single table to use. You can still do this using the helper function Choose(). We want to look up Joe’s salary from the two separate tables below. Excell-choose

=VLOOKUP(“EN878”,CHOOSE({1,2},A2:B5,D2:E5),2,False)

and the answer will be $38,000 because the CHOOSE() function first constructs a two column table consisting of A2:A5 and E2:E5. VLOOKUP() will then run over this table to find the value.

Hope this has helped clarifying CHOOSE() using arrays.

How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top
Back to top