CHOOSE function in Excel with formula examples

This tutorial explores the versatile Excel CHOOSE function, demonstrating its syntax, basic usage, and advanced applications beyond its initial appearance. While seemingly simple, combining CHOOSE with other functions unlocks powerful capabilities. The core function retrieves a value from a list based on its position.

• Excel CHOOSE Function: Syntax and Basic Uses
• Advanced Applications of the CHOOSE Function:
  • Replacing Nested IF Statements
  • Generating Random Data
  • Performing Left Lookups (alternative to VLOOKUP limitations)
  • Calculating the Next Working Day
  • Retrieving Custom Day/Month Names from Dates

Excel CHOOSE Function: Syntax and Basic Uses

The CHOOSE function returns a value from a list, determined by its index number. Available in Excel 365, 2019, 2016, 2013, 2010, and 2007, its syntax is:

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

• index_num: (Required) The position (1 to 254) of the desired value. Can be a number, cell reference, or formula.
• value1, value2, ...: (Required and Optional) Up to 254 values to choose from. Can be numbers, text, cell references, formulas, or named ranges.

Example: =CHOOSE(3, "Mike", "Sally", "Amy", "Neal") returns "Amy" (3rd value).

Key Considerations:

1. Value limit: A maximum of 254 values are allowed.
2. index_num range: Must be between 1 and the number of values; otherwise, a #VALUE! error occurs.
3. Fractional index_num: Truncated to the nearest whole number.

Advanced Applications of the CHOOSE Function

These examples showcase CHOOSE's power when combined with other functions:

1. Replacing Nested IF Statements:

Instead of complex nested IF statements, CHOOSE offers a concise alternative for conditional value returns.

Example: Assign grades based on scores:

Result	Score Range
Poor	0 - 50
Satisfactory	51 - 100
Good	101 - 150
Excellent	>150

Nested IF: =IF(B2>=151, "Excellent", IF(B2>=101, "Good", IF(B2>=51, "Satisfactory", "Poor")))

CHOOSE Alternative: =CHOOSE((B2>=0) (B2>=51) (B2>=101) (B2>=151), "Poor", "Satisfactory", "Good", "Excellent")

This leverages TRUE/FALSE values (1/0) to determine the index. Error handling (e.g., IFERROR) can prevent #VALUE! errors if no condition is met.

2. Generating Random Data:

Combine CHOOSE with RANDBETWEEN to create random data selections.

Example: Generate random exam results:

=CHOOSE(RANDBETWEEN(1,4), "Poor", "Satisfactory", "Good", "Excellent")

Note: RANDBETWEEN is volatile; results change with worksheet updates. Use "Paste Special" to fix values.

3. Left VLOOKUP:

Circumvent VLOOKUP's limitation of searching only the leftmost column using CHOOSE.

Example: Retrieve scores given student names (score column is to the left of names).

=VLOOKUP(E1,CHOOSE({1,2}, B2:B5, A2:A5),2,FALSE)

CHOOSE swaps the column order within the VLOOKUP's table_array argument.

4. Determining the Next Working Day:

Calculate the next workday (Monday-Friday).

=TODAY() CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2)

This adds the appropriate number of days based on the current day of the week.

5. Custom Day/Month Names:

Retrieve day or month names in custom formats.

Day: =CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa")

Month: =CHOOSE(MONTH(A2), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

These examples highlight the CHOOSE function's flexibility and potential for creating efficient and readable Excel formulas. Download the practice workbook for hands-on experience.

The above is the detailed content of CHOOSE function in Excel with formula examples. For more information, please follow other related articles on the PHP Chinese website!