Lookup function
Excel CHOOSE function
CHOOSE returns one value from a list you type, picked by its position: CHOOSE(2,"Ground","Express","Overnight") gives Express. It turns small codes such as 1, 2 and 3 into labels or rates without a lookup table.
=CHOOSE(index_num, value1, [value2], ...)Arguments
index_num- Which value to return: 1 for value1, 2 for value2, and so on. Decimals are cut off, so 2.9 counts as 2.
value1- The first value. Each value can be a number, text, a cell, a range or a formula.
value2- Optional. More values, up to 254 in all.
CHOOSE examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Ship code | Weight (kg) | Order date | |
| 2 | SO-2201 | 2 | 3.5 | Aug 14, 2026 | |
| 3 | SO-2202 | 1 | 12 | ||
| 4 | SO-2203 | 3 | 0.8 | ||
| 5 | SO-2204 | 2 | 6.2 | ||
| 6 | SO-2205 | 1 | 1.4 |
Download this sheet as an Excel practice file (.xlsx)
Result: Express
B2 holds 2, so CHOOSE returns the second label: Express.
Result: 24
Turns ship code 3 into its flat rate, 24. Fill it down to price every order.
Result: Q3
MONTH gives 8 for August, and the eighth value is Q3. Reorder the list for a fiscal year that starts in another month.
Result: SO-2204
CHOOSE({1,2},...) puts the weight column first and the order column second, so VLOOKUP can look left: SO-2204.
How to use CHOOSE
- Click the cell for the result.
- Type =CHOOSE( and the cell or number that says which item you want.
- Add a comma and list the values in order, separated by commas.
- Close the bracket and press Enter.
Common CHOOSE mistakes
An index past the end of the list
CHOOSE(4,"a","b","c") is #VALUE! because there is no fourth value. 0 and negative numbers are #VALUE! too. Check the codes in the column, or wrap it in IFERROR.
Long lists typed into the formula
Once a list passes about 5 items or changes often, put it in a small table and use XLOOKUP or INDEX instead. A table is easier to read and to update.
Expecting it to match text
CHOOSE needs a position number. To turn a text code such as "EXP" into a value, use XLOOKUP on a table, or IFS.
CHOOSE FAQ
What does the CHOOSE function do in Excel?
It returns the value at a given position in a list you type into the formula. =CHOOSE(3,"Low","Medium","High") returns High.
How do I do a left lookup with VLOOKUP?
Swap the columns with CHOOSE: =VLOOKUP(6.2,CHOOSE({1,2},C2:C6,A2:A6),2,FALSE) searches column C and returns column A. In Excel 365, XLOOKUP looks left without this trick.
Why does CHOOSE return #VALUE!?
The index is less than 1 or larger than the number of values, or it is text that is not a number. A blank index cell counts as 0, which is also #VALUE!.
Can CHOOSE return a range?
Yes. =SUM(CHOOSE(2,B2:B6,C2:C6)) adds C2:C6, because CHOOSE hands the second range to SUM.
Related functions
INDEXValue at a positionXLOOKUPModern lookup in any directionVLOOKUPVertical lookupIFSFirst true test wins
Updated