
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:
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.=SUM(CHOOSE(A1,A3:C9,E3:J9,M3:Q9))
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.
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.=VLOOKUP(“EN878”,CHOOSE({1,2},A2:B5,D2:E5),2,False)
Hope this has helped clarifying CHOOSE() using arrays.
How do your Excel skills stack up?
Test NowNext up:
- DMOC: The evolution of Microsoft's Official Courseware
- Reverse engineering a nested formula in Excel
- Top 10 posts you may have missed from January
- The science of presenting - Part 2
- Control your code with GitHub
- Introduction to transitions and animations in PowerPoint 2010/13
- Normalising your database: Third Normal Form (3NF) – Part 3
- Master mail merge to email in Microsoft Word
- How to be positive and enthusiastic at work when you don't feel like it!
- Manage emails in Outlook using the 4 Ds
Previously
- Windows 10 - Technical Preview
- How to make your New Year goals a reality
- Three really handy Excel keyboard shortcuts
- Happy Australia Day!
- 5 super cool technology gadgets from the past...
- Microsoft Sway - Will you be swayed?
- The science of presenting (Part 1)
- New Horizons' top 10 blog posts of 2014
- Monitoring user connections to Office 365
- Reduce your PDF file size in Acrobat XI