Microsoft Office Tutorials and References

In Depth Information

**Using the CHOOSE Function for Simple Lookups**

reveal details on how to use the lookup functions and how to combine them to

create powerful results.

Using the

Using the
CHOOSE

CHOOSE
Function for Simple Lookups

Function for Simple Lookups

Most lookup functions require you to set up a lookup table in a range on the

worksheet. However, the CHOOSE function enables you to specify up to 254

choices right in the syntax of the function. The formula that requires the

lookup should be able to calculate an integer from 1 to 254 in order to use

the CHOOSE function.

Syntax

CHOOSE(index_num,value1,value2,...)

The CHOOSE function chooses a value from a list of values, based on an in-

dex number. The CHOOSE function takes the following arguments:

•
index_num

index_num
—
This specifies which value argument is selected. in-

dex_nummust be a number between 1 and 254 or a formula or reference

to a cell containing a number between 1 and 254:

•
If index_numis 1, CHOOSE returns value1; if it is 2, CHOOSE re-

turns value2; and so on.

•
If index_numis a decimal, it is rounded down to the next lowest in-

teger before being used.

•
If index_numis less than 1 or greater than the number of the last

value in the list, CHOOSE returns a #VALUE! error.

•
value1,value2,...

value1,value2,...
—
These are one to 254 value arguments from which

CHOOSE selects a value or an action to perform based on index_num.

The arguments can be numbers, cell references, defined names, formu-

las, functions, or text.

The example in
Figure 12.21
shows survey data from a number of respondents.

Columns B:F indicate their responses on five measures of your service.

Column G calculates an average that ranges from 1 to 5. Say that you want

to add words to column H to characterize the overall rating from the re-

spondent. The following formula is used in cell H4:

=CHOOSE(G4,"Strongly

Disagree","Disagree","Neutral","Agree","Strongly Agree")