The excel CHOOSE function

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 nth 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.

4 Comments

  1. Tiago Espinha

    Well 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).

    1. 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! 🙂

  2. 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

  3. ska

    I’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

Comments are closed.