Excel CHOOSE Function
last modified April 4, 2025
The CHOOSE function is a versatile lookup function in Excel that
returns a value from a list based on a specified position. This tutorial
provides a comprehensive guide to using the CHOOSE function with
detailed examples. You'll learn basic syntax, practical applications, and
advanced techniques to master this powerful Excel function.
CHOOSE Function Basics
The CHOOSE function selects and returns a value from a list of
arguments based on an index number. It can handle up to 254 values and is
useful for creating dynamic formulas.
| Component | Description |
|---|---|
| Function Name | CHOOSE |
| Syntax | =CHOOSE(index_num, value1, [value2], ...) |
| Arguments | Index number + 1-254 values |
| Return Value | Selected value from the list |
This table breaks down the essential components of the CHOOSE
function. It shows the function name, basic syntax format, argument limits, and
return value characteristics.
Basic CHOOSE Example
This example demonstrates the simplest use of the CHOOSE function with hard-coded values.
=CHOOSE(2, "Apple", "Banana", "Cherry")
This formula returns "Banana" because it's the second item in the list (index 2). The first argument is the position number, followed by the values to choose from.
CHOOSE with Cell References
A more practical use of CHOOSE involves referencing cells for both
the index and values. Here's an example with cell references.
| A | B | C |
|---|---|---|
| 2 | Red | |
| Green | ||
| Blue | ||
| =CHOOSE(A1, B1, B2, B3) |
The table shows a simple spreadsheet with an index number in A1 and color values in B1:B3. The CHOOSE formula in C4 returns the color based on the index.
=CHOOSE(A1, B1, B2, B3)
This formula uses the value in A1 (2) to select from B1 ("Red"), B2 ("Green"), and B3 ("Blue"). The result will be "Green". This demonstrates how CHOOSE can create dynamic outputs.
CHOOSE with Ranges
CHOOSE can return entire ranges, which is useful for creating dynamic references in other formulas. This example shows this capability.
| A | B | C |
|---|---|---|
| 1 | Q1 | 100 |
| Q2 | 200 | |
| Q3 | 300 | |
| =SUM(CHOOSE(A1, B1:C1, B2:C2, B3:C3)) |
This table demonstrates how CHOOSE can select different ranges based on an index. The formula in C4 sums either Q1, Q2, or Q3 data based on the value in A1.
=SUM(CHOOSE(A1, B1:C1, B2:C2, B3:C3))
If A1 contains 2, this formula selects the range B2:C2 ("Q2" and 200) and sums it. Since "Q2" is text, it's ignored, returning 200. This technique is powerful for dynamic data analysis.
CHOOSE with Nested Functions
CHOOSE can be nested with other functions to create complex logic. This example shows CHOOSE nested with WEEKDAY.
| A | B |
|---|---|
| 5/15/2023 | |
| =CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") |
The table demonstrates using CHOOSE to convert a weekday number to a day name. The WEEKDAY function returns a number 1-7, which CHOOSE uses to select the correct day abbreviation.
=CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
For May 15, 2023 (a Monday), WEEKDAY returns 2, so CHOOSE returns "Mon". This combination is useful for creating user-friendly date displays without complex formatting.
CHOOSE for Scenario Analysis
CHOOSE is excellent for scenario analysis where you need to switch between different calculation methods. This example demonstrates this application.
| A | B | C |
|---|---|---|
| 2 | 1000 | |
| =B1*CHOOSE(A1, 0.1, 0.15, 0.2) |
This table shows how CHOOSE can select different percentage rates based on a scenario number. The formula in C2 calculates different commission rates depending on the value in A1.
=B1*CHOOSE(A1, 0.1, 0.15, 0.2)
With A1=2 and B1=1000, this formula calculates 1000 * 0.15 = 150. This approach lets you easily switch between different calculation parameters without complex IF statements.
CHOOSE with VLOOKUP Alternative
CHOOSE can create a simplified lookup table as an alternative to VLOOKUP for small datasets. This example shows this technique.
| A | B | C |
|---|---|---|
| 3 | ||
| =CHOOSE(A1, "Bronze", "Silver", "Gold", "Platinum") |
The table demonstrates using CHOOSE as a simple lookup where numeric codes (1-4) correspond to membership levels. This is more efficient than VLOOKUP for small, static lists.
=CHOOSE(A1, "Bronze", "Silver", "Gold", "Platinum")
When A1 contains 3, this formula returns "Gold". While limited to about 254 items, this method is faster to set up than VLOOKUP for small, unchanging lookup tables.
The CHOOSE function is a versatile tool for creating dynamic
formulas in Excel. From simple value selection to complex scenario analysis,
CHOOSE offers elegant solutions. Mastering its various
applications will significantly improve your spreadsheet efficiency. Remember
that CHOOSE is limited to 254 values and requires numeric index
positions starting from 1.
Author
List all Excel Formulas.