Excel: I Don't Want to Use a Lookup Table to Choose One of Five Choices

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have to choose among five choices. I don't want to nest a bunch of IF functions, and I really don't want to add a lookup table off to the side of my worksheet. Is there a function that will allow me to specify the possible values in the function?

Strategy: In this situation, you can use the CHOOSE function.

The first argument of the CHOOSE function is a number from 1 to 254. You then specify the values for each possible number, entered as separate arguments. For example, =CHOOSE(2,"Red","Green","Blue") would return Green.

It is a bit frustrating that you must specify each choice as a separate argument. I always want to specify a single range such as Z1:Z30 as the list of arguments but this will not work. However, if you already have the list of arguments somewhere, you don't need to use CHOOSE; you can easily use VLOOKUP or INDEX in such a case.

Here, a CHOOSE function returns the description of the plan number chosen in cell B5.

  1. Choose is great for short lists.

    Gotcha: CHOOSE works only if your plan codes are 1, 2, 3, and so on. If you have plan codes of A, B, C, and so on, you should probably use a lookup table in an out-of-the way location. Or you could use =CODE(B5)-64 to convert the A to a 1 and so on.

    Additional Details: If you have a list of plan names somewhere, you might be tempted to enter =CHOOSE(B5,B7,B8,B9,B10,B11). Instead, it is easier to use =INDEX(B7:B11,B5). The INDEX function will return the B5th item from the list in B7:B11.

  2. Switch to INDEX if you have a list in a range.