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.

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.

This article is an excerpt from Power Excel With MrExcel
Title photo by Edgar Castrejon on Unsplash