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.


  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:


    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:

    I also came across a really cool way of calculating the first Monday of the month here:


Comments are closed.