- Excel Book Excerpt
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, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
I Don’t Want to Use a Lookup Table to Choose One of Five Choices
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.
Figure 464 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.
Figure 465 Switch to INDEX if you have a list in a range.
For more resources for Microsoft Excel: