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 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.
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 — 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,... — 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: