# The excel CHOOSE function

felienne
June 18, 2012

Today I learnt another cool Excel function I was previously unaware of, the CHOOSE function. As arguments it takes one value *n*, followed by a list of values that. CHOOSE will return the *n*th value of that list.

=CHOOSE(A1,”A”,”B”,”C”,”D”,”E”,”F”,”G”) returns “C” if A1 contains 3

You get the idea. I do not know for what scenario’s this function comes in handy.

*Related*

Tiago EspinhaWell this is basically an array access in Java, C#, C, you name it…

int A1 = 3;

char[] letters = new char[]{‘A’,’B’,’C’,’D’,’E’,’F’,’G’};

letters[A1] // returns “C”

I don’t know in which language this particular piece of code works, but it will work in at least one language I’m sure 🙂

P.S. I also don’t know for what Excel use case this will be useful but people can get *really* creative with Excel… (which you probably know better than me).

admin(Post author)You are right, it is a bit like an array operation, but with funky syntax. And I am still not sure on how this is used. That calls for a paper! 🙂

admin(Post author)I dug into this formula a bit more, and it turns out there is another, quite similar, way to do this:

=VLOOKUP(A1,{1,”A”;2,”B”;3,”C”;4,”D”;5,”E”;6,”F”;7,”G”},2,0)

What do you think, is this better or even worse?

Pro: The VLOOKUP is a common one, so it is more clear that something is to be found here.

Con: This version is longer

skaI’m also quite intrigued with this formula. Been looking for a useful scenario, but find it hard to find one 🙂

Much as I dislike VLOOKUP (prefer INDEX(MATCH) any day 🙂 ) using CHOOSE in array format can enable one to do a “reverse VLOOKUP”, which is not possible with the normal version of VLOOKUP. (This is not a problem with INDEX(MATCH), by the way.)

Lifting a comment from here (1):

“CHOOSE can also be used to piece together an array of non-contiguous ranges. This can be very useful if you want to do a VLOOKUP to the left. (I.e. looking up the value in a column on the right side of a table and retrieving the value from a column on the left side of the table.) For example if your table is the range A1:C10 and you want to lookup a value in column C and return the corresponding value in column A you could use the following formula:

=VLOOKUP(lookup_value,CHOOSE({1,2},C1:C10,A1:A10),2,0)”

I also came across a really cool way of calculating the first Monday of the month here: http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=106:choose-function-in-excel&catid=78&Itemid=474

(1) http://blog.contextures.com/archives/2011/01/06/30-excel-functions-in-30-days-05-choose/#div-comment-10527