Picking Items With CHOOSE

The CHOOSE function picks one item from a list based on a position number. You give it an index, then a series of values, and it returns the value sitting at that index.

Think of it as a numbered menu: give CHOOSE the number 3 and it hands back the third item, regardless of what that item is.

The structure is CHOOSE(index_num, value1, value2, value3, ...).

• index_num is a number from 1 up to the count of values.
• Each value can be text, a number, a cell reference, or even a formula.

The index is one-based: an index of 1 returns value1, an index of 2 returns value2, and so on.

Say a priority level 1, 2, or 3 lives in A2. You want a word instead of a number. CHOOSE turns the number directly into the matching label.

If A2 is 1 you get "Low", if it is 2 you get "Medium", and if it is 3 you get "High". The number itself is the position selector.

The index has to land between 1 and the number of values you provided. If A2 is 0, 4, or blank in the example above, CHOOSE returns the #VALUE! error.

Decimals are simply truncated, so 2.9 is treated as 2. To stay safe, make sure your index can only produce numbers that point to real positions in your list.

A very common use is converting a month number into a month name. With a month number in A2, CHOOSE lists all twelve names in order.

This is handy when a date function gives you MONTH(...) as a number but you want to display the readable name.

The values do not have to be typed literals. They can be cell references, so CHOOSE pulls a live value from elsewhere on the sheet.

Here, depending on the index in A2, the formula returns the contents of B1, C1, or D1. Change those cells and the result follows.

Each value can be a formula too. This lets CHOOSE act like a small switchboard for different calculations.

Suppose a tier in A2 selects a discount rate to apply to a price in B2. Index 1 gives 5% off, index 2 gives 10% off, and index 3 gives 20% off.

CHOOSE and SWITCH solve similar problems but differently:

• CHOOSE selects by position. The index must be a sequential number 1, 2, 3.
• SWITCH selects by matching a value, which can be any code or text.

Use CHOOSE when your selector is already a clean counting number. Use SWITCH when you match arbitrary codes like "STD" or "EXP".

Let us build a quick quarter label from a month number in A2. We can wrap CHOOSE around a calculation that converts the month into 1 through 4.

ROUNDUP(A2/3,0) turns months 1 to 3 into quarter 1, months 4 to 6 into quarter 2, and so on. CHOOSE then maps that to a label.

Because an out-of-range index causes #VALUE!, it is wise to protect the formula. Wrapping it in IFERROR gives a friendly fallback.

Now if the index is ever invalid, the user sees "Invalid" instead of an error code, which keeps the sheet looking clean and professional.

Keep CHOOSE working smoothly:

• Make sure the index is a whole number from 1 to your value count.
• List values in the exact order the index expects.
• Wrap in IFERROR if the index might fall out of range.
• Prefer CHOOSE when the selector is a natural counting number; prefer SWITCH for arbitrary codes.

Test your understanding of how CHOOSE selects values.

You learned to select the nth item from a list using CHOOSE. Key points:

• Syntax is CHOOSE(index_num, value1, value2, ...) and the index is one-based.
• An out-of-range index returns #VALUE!; guard it with IFERROR.
• Values can be text, numbers, cell references, or formulas.
• Use CHOOSE when the selector is a counting number, SWITCH when matching arbitrary codes.