I Don’t Want to Use a Lookup Table to Choose One of Five Choices

May 06, 2022 - by Bill Jelen

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.

If your lookup values are numbers 1 through N, then CHOOSE is an easy way to do the LOOKUP. — Figure 456. 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.

Instead of CHOOSE, you can use INDEX. — Figure 457. Switch to INDEX if you have a list in a range.

This article is an excerpt from Power Excel With MrExcel

Title photo by Edgar Castrejon on Unsplash