CHOOSE Function

Picks one value from a list, based on a position number.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

The chosen value, cell or range

Taught hands-on in Financial Modeling · Scenarios & Sensitivity module

Overview

CHOOSE works like a numbered menu. You give it a number and a list of options, and it hands back the option in that position. =CHOOSE(2, "Red", "Green", "Blue") returns Green because Green is option 2.

It's a tidy replacement for a stack of nested IFs when you're turning a number (1, 2, 3…) into something else — a label, a rate, or even a whole range to calculate on.

Syntax

=CHOOSE(index_num, value1, [value2], …)

Arguments

ArgumentRequired?What it does
index_numYesWhich option to return. 1 returns value1, 2 returns value2, and so on. Decimals are rounded down.
value1YesThe first option. Can be a number, text, cell, range or formula.
value2, …OptionalMore options — up to 254 in total.

Examples

Example 1 — Turning a number into a label

Our survey stores ratings as 1–3. We want a readable label beside each one.

C2fx=CHOOSE(B2, "Poor", "Okay", "Great")
ABC
1CustomerRatingLabel
2Ana3Great
3Ben1Poor
4Cara2Okay
=CHOOSE(B2, "Poor", "Okay", "Great") Explain

Compare that to the nested IF version — =IF(B2=1,"Poor",IF(B2=2,"Okay","Great")) — and you can see why CHOOSE is easier to read.

Example 2 — Quarter from a date

MONTH returns 1–12, so we can list a quarter for each month:

=CHOOSE(MONTH(A2), "Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4") Explain

If A2 is 14-Aug-2026, MONTH returns 8, and option 8 is Q3.

Running a fiscal year that starts in July? Just reorder the list — no other changes needed. That flexibility is CHOOSE's superpower.

Example 3 — Choosing a range to calculate on

The options don't have to be single values. Here, a dropdown in F1 (1, 2 or 3) decides which month column gets totalled:

F2fx=SUM(CHOOSE(F1, B2:B4, C2:C4, D2:D4))
ABCDEF
1RepJanFebMarMonth #2
2Ana400520610Total1,560
3Ben350480390
4Cara500560450
=SUM(CHOOSE(F1, B2:B4, C2:C4, D2:D4)) Explain
  1. F1 is 2, so CHOOSE returns the second range: C2:C4.
  2. SUM(C2:C4) adds 520 + 480 + 560 = 1,560.

Common mistakes

  • #VALUE! errors. index_num is less than 1 or greater than the number of options you listed.
  • Passing a range as the list. =CHOOSE(2, A1:A3) doesn't pick A2 — it treats A1:A3 as one option. Use INDEX(A1:A3, 2) for that.
  • Hard-coding long lists. If your options live in cells or change often, INDEX or XLOOKUP against a table is easier to maintain.

Notes

  • index_num of 2.9 is treated as 2.
  • Passing an array as index_num returns several results at once: =CHOOSE({1,3}, "A", "B", "C") spills A and C.
  • Related functions: INDEX, SWITCH, IFS, XLOOKUP, CHOOSECOLS.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.