Knowledge base / Excel

Excel CHOOSE: select a value or range by position

Select an option by position, switch a summed range, and separate CHOOSE's index from a key that must be looked up in a table.

Kajo RudzińskiPublished Updated 4 min read

A selector arm chooses the middle of three trays holding different shapes

When I have three predefined options and a number identifying the chosen position, I use CHOOSE. Its Polish name is WYBIERZ. My first question is where that number comes from. A position in a short list fits the function. A customer or product identifier needs a lookup operation first.

All data below is synthetic. These basic examples use functions documented for Excel Microsoft 365, 2024, 2021, 2019 and 2016. English formulas use comma separators here; regional configuration determines the actual separator required in your workbook.

Select the second option

The syntax is CHOOSE(index_num,value1,[value2],...). Positions start at 1. The first argument tells Excel which subsequent value to return. That index argument does not itself count as one of the available options.

=CHOOSE(2,"Coffee","Tea","Juice")

The result is Tea, the second option. The corresponding Polish expression, with typical Polish separators, is:

=WYBIERZ(2;"Kawa";"Herbata";"Sok")

Microsoft documents a maximum of 254 options. I use CHOOSE for short lists whose order remains understandable. If the list grows or changes regularly, a reference table usually makes the data easier to maintain than an increasingly long formula.

Use cell references without overwriting headers

Put the following table in A1:B4. Enter the header choice in E1 and the number 2 in E2. Put the output formula in F2. This keeps the input separate from the product table and its headers.

A: ID B: Product
101 Coffee
102 Tea
103 Juice
=CHOOSE(E2,B2,B3,B4)

With E2 equal to 2, the result is Tea. With 3, it is Juice. Entering 102 does not find product ID 102. There are only three options, so that number is outside the list. This distinction prevents a common confusion between a position and a business key.

Fractional indexes and boundaries

The CHOOSE documentation states that fractional indexes are truncated before selection. An index of 2.9 therefore selects the second option. Describing the argument as accepting only whole numbers would hide behavior that may matter when reviewing input mistakes.

Index 0, or index 4 with only three options, produces #VALUE!. In Polish Excel, that error is #ARG!. I check the permitted selection range before hiding the error with a blank output. An empty cell would make an invalid choice much harder to distinguish from an intentionally missing result.

Switch the range being summed

In a separate worksheet, enter 10 and 20 in B2:B3, and 4 and 6 in C2:C3. Let E2 choose the range: 1 for the first, 2 for the second. Put this formula in F2.

=SUM(CHOOSE(E2,B2:B3,C2:C3))

Choice 1 selects B2:B3 and gives a total of 30. Choice 2 selects C2:C3 and gives 10. CHOOSE supplies the reference, then SUM uses that range. Microsoft’s documentation includes the same mechanism of passing a selected range to another function.

A switch routes one of two sets of golden cubes into a common container

Choose a VLOOKUP output column

In another worksheet, prepare A1:C4 with headers ID, Product, Points. Use rows 101/Coffee/10, 102/Tea/8 and 103/Juice/6. Put lookup ID 102 in E2 and selection 1 or 2 in F2. Enter the formula in G2.

=VLOOKUP(E2,$A$2:$C$4,CHOOSE(F2,2,3),FALSE)

Selection 1 supplies column number 2 and returns Tea. Selection 2 supplies column 3 and returns 8. VLOOKUP finds the ID; CHOOSE only decides which output column it returns. FALSE requests an exact match.

When another function fits better

For searching by product ID, continue with VLOOKUP. If you need to search a name and return an ID to its left, consider XLOOKUP in a suitable version. Microsoft explicitly notes that XLOOKUP is unavailable in Excel 2016 and 2019.

I avoid building an array-based left-lookup trick in this entry. The key point is separating the selected position from the value being searched. Rearranging columns does not make a numeric ID match a column of names. Establish the lookup column and desired result first.

Try a small exercise

For the three products, predict choices 1, 3, 0 and 4 before entering them. Write down either the expected value or the expected error. Then change the second product name in the source and decide which output should change. This distinguishes a cell reference from text hard-coded in a formula.

Finally, label the selector so another reader knows what each number means. A switch offering 1 and 2 is useful only when its alternatives are explained. I prefer a short description beside the input to a formula whose author is the only person who remembers the option order.

Key takeaways

What does CHOOSE's first argument mean?

The position in its list of value arguments. One selects the first option and two selects the second. It is not a record identifier to search for in a table.

What happens with index 2.9?

The fractional part is truncated, so the second option is selected if it exists. Validate input separately if users are only allowed to enter whole-number choices.

Can CHOOSE return a range?

Yes. Another function such as SUM can use the selected range reference. You do not need to add each cell individually.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.