Excel CHOOSE Function Not Working? Common Fixes

If Excel’s CHOOSE function is not working, first check that its index is a whole number from 1 through the number of choices you supplied, and that every argument is separated correctly. An index outside that range commonly causes #VALUE!; a misspelled function, blocked array result, or unsuitable formula structure can cause other problems.

What the CHOOSE function does

CHOOSE returns one item from a list of values, using a number to select which item. Its syntax is CHOOSE(index_num, value1, [value2], ...). The first value follows index 1, the second follows index 2, and so on. It is useful for a short, fixed set of alternatives—for example, translating a quarter number into a quarter label or mapping a small numeric code to a description.

With the number 2 in A2, the following formula returns the text South. The values are listed directly in the formula, so CHOOSE does not search a table or infer which value matches a text label.

Assuming A2 contains 2, this formula selects the second listed option:

=CHOOSE(A2,"North","South","West")

CHOOSE can return text, numbers, cell references, or results from other formulas. It can accept as many as 254 value arguments, but that does not mean a long list is always the best design. For many choices, a small lookup table is usually easier to edit and audit.

Check the index and the number of choices

The most common cause of failure is that the index does not point to an available value. If the formula supplies three choices, the usable index range is 1 to 3. An index of 0, a negative number, or 4 is outside the list and returns #VALUE!. A blank index cell is often treated like zero in this calculation, which can make a formula fail even though the visible formula looks reasonable.

Count the value arguments in the formula, then compare that count with the index. A frequent editing mistake is adding a new option in one part of a workbook but not adding its matching CHOOSE argument. Another is using a position that starts at zero because the source data uses zero-based numbering. CHOOSE starts at 1, so a source code of 0 needs to be converted or handled separately.

Assuming A2 contains an index from 1 to 3, this formula returns Low, Medium, or High. If A2 is 4, the formula has no fourth value to return:

=CHOOSE(A2,"Low","Medium","High")

Excel truncates a fractional index to an integer before using it. That behavior can conceal bad input: 2.8 selects the second value rather than reporting that the index is not a whole number. If a fractional value is possible and should be rejected, validate the input or add a separate check instead of relying on CHOOSE to catch it.

Assuming A2 contains 2.8, this formula returns February because Excel truncates the index to 2:

=CHOOSE(A2,"January","February","March")

Trace the value feeding the formula

A formula may be structurally correct while a referenced cell contains an unexpected value. Click the index cell and inspect its displayed value and underlying formula. Check whether a preceding calculation returns zero, a negative number, a decimal, or an error. If the index comes from a dropdown, confirm that the selected entry produces the numeric position the CHOOSE formula expects.

On Excel for Microsoft 365 for Windows, select the formula cell and use Formulas > Evaluate Formula to step through the calculation. You can also select a portion of a formula in the formula bar and press F9 to evaluate that portion; press Esc afterward so you do not replace the formula with the evaluated result. On a Mac, the Evaluate Formula command is available from the Formulas tab in current desktop Excel, though ribbon layout can differ by release. Excel for the web has fewer formula-auditing tools, so inspect the referenced cells directly if the desktop command is unavailable.

When the index should only be accepted within a known range, make that condition visible. For example, a separate validation message can distinguish an invalid index from a problem with the selected value. Avoid hiding every error with a broad error-trapping formula before identifying the cause: doing so can turn a useful diagnostic into a blank cell that is harder to fix.

Look for spelling, quotes, and argument separators

CHOOSE is an old, widely supported Excel function, so a version that lacks the function is rarely the problem. If you see #NAME?, check whether the function name is spelled correctly, whether the formula starts with an equals sign, and whether text values have straight double quotation marks. A text option typed without quotation marks may be interpreted as a name that Excel cannot find.

Excel’s argument separator depends on regional settings. Many installations use commas, while some use semicolons. If a comma-separated formula is rejected when you enter it, check the separator used by another working formula in the same Excel installation and use the separator your settings expect. The examples here use commas, as requested for standard English-language Excel syntax. Do not confuse the argument separator with punctuation that is part of a quoted text value.

Also check for a missing separator or closing parenthesis after editing the formula. Excel usually identifies a syntax problem when you enter the formula, but a long formula with nested functions can make the missing character difficult to locate. Temporarily simplify it to a short CHOOSE formula, confirm that it works, and then restore the other parts one at a time.

Check references and selected values

CHOOSE can return a cell reference as one of its options. In that case, the chosen reference may itself contain an error, point to an unintended cell, or be affected when the formula is copied. Use absolute references where the lookup options must stay fixed, and verify that the index and the selected reference are both pointing where you expect.

For example, with an index in A2 and options in B2 through D2, the formula below returns the contents of D2 when A2 is 3. If D2 contains an error, CHOOSE returns that error; it does not repair the selected value.

Assuming A2 contains 3 and B2:D2 contain the three choices, this formula returns the value in D2:

=CHOOSE(A2,B2,C2,D2)

When copying a formula down a column, relative references move with each row. That is often wanted for the index, but it may not be wanted for a fixed list of options. If the list is in B2:B4 and each row should use that same list, lock those references with dollar signs before filling the formula down. Check the first and last copied formulas to make sure the references did not drift.

Understand array results and spill errors

CHOOSE can work with arrays as well as a single index. In Microsoft 365, an array result may spill into nearby cells. If the intended output range is occupied, Excel reports #SPILL!. Clear the blocking cells, remove merged cells in the spill area, or move the formula to a location with enough empty space. A formula that returns only one value does not need an empty spill range.

For a simple illustration, suppose A2 contains Apple and B2 contains Pear. In a US-style Excel installation, the array constant in this example asks CHOOSE to return both referenced values as a horizontal array. Microsoft 365 can display the results in adjacent cells when they are available. Array-constant punctuation can vary with regional settings, and older Excel versions may require legacy array-entry behavior for some array formulas.

Assuming A2 contains Apple and B2 contains Pear, enter this formula in an empty area; in Microsoft 365 it returns Apple and Pear in adjacent cells:

=CHOOSE({1,2},A2,B2)

If you see #SPILL!, select the error indicator to learn what is blocking the result. If you see #VALUE! instead, inspect the index values in the array and make sure each position has a corresponding value argument. Array behavior can be confusing because a formula may work in a single cell but produce a wider result when its index is an array.

Choose CHOOSE or a lookup formula?

CHOOSE is a good fit when the choices are few, fixed, and naturally identified by position. It is compact for a small mapping such as 1 to Bronze, 2 to Silver, and 3 to Gold. The trade-off is that the mapping is embedded in the formula. Updating the options means editing formulas, and a long list of choices becomes difficult to maintain.

If your options are already in a worksheet table, INDEX can select an item by position without repeating the list in every formula. With the options in B2:B4 and the position in A2, the formula below returns the second item when A2 is 2. INDEX is available in older Excel versions as well as current versions.

Assuming A2 contains 2 and B2:B4 contain the choices in order, this formula returns the value in B3:

=INDEX(B2:B4,A2)

If you need to find a matching code rather than select by position, use a lookup approach. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. With codes in A2:A4, matching descriptions in B2:B4, and the code to find in D2, the example returns the matching description or Not found when there is no match.

Assuming A2:A4 contain codes, B2:B4 contain descriptions, and D2 contains the code to search for, this formula returns the corresponding description:

=XLOOKUP(D2,A2:A4,B2:B4,"Not found")

Use a lookup table when choices change regularly or other people need to maintain the mapping. Use CHOOSE when a short fixed list is clearer inside the formula. Neither method is automatically more accurate: the right choice depends on whether the selection is based on a position or on matching a real key such as a product code.

A practical troubleshooting sequence

Work through the checks in order so you can identify the actual cause rather than replacing the formula unnecessarily:

Read the exact error shown in the cell. #VALUE!, #NAME?, and #SPILL! point to different kinds of problems.
Count the value arguments and confirm that the index is between 1 and that count. Check the underlying value, not just its displayed formatting.
Confirm that the formula uses the argument separator required by your regional settings and that its parentheses and quotation marks are complete.
Inspect every referenced option. A selected cell can contain an error or an unexpected blank even when the CHOOSE formula is valid.
If the formula returns an array, clear the expected output area and check for merged cells or other spill blockers.
Test a simplified formula in an empty cell, then reintroduce references or nested calculations in small steps.

This process also helps separate a CHOOSE problem from a problem in a preceding formula. If a standalone test works but the original does not, focus on the index calculation, selected reference, or nested expression instead of the function itself.

Frequently Asked Questions

Why does CHOOSE return #VALUE! in Excel?

The usual reason is that the index is less than 1 or greater than the number of supplied values. Check whether the index cell is blank, zero, negative, or unexpectedly large, and count the choices in the formula. A fractional index is truncated to an integer, so it may select an unexpected option rather than produce an error.

Does CHOOSE start counting at zero or one?

It starts at 1: index 1 selects the first value, and index 2 selects the second. If your source codes start at zero, adjust or translate them before using CHOOSE, and decide separately how the zero case should be handled.

Why does Excel say there is a problem with my CHOOSE formula?

Check the formula’s spelling, opening and closing parentheses, quotation marks, and argument separators. Some regional settings require semicolons instead of commas between arguments. Also make sure text values are quoted and that every value argument is separated from the next one.

Can CHOOSE return a cell reference?

Yes. A value argument can be a cell reference, and CHOOSE can return the selected cell’s value. Confirm that the reference points to the intended cell and that it does not contain an error. Lock references with dollar signs if they must stay fixed when the formula is copied.

Why does a CHOOSE formula show #SPILL!?

The formula is producing an array result, but one or more cells needed for the output are not available. Clear the cells in the spill area, check for merged cells, or move the formula. If you only want one answer, check whether the index is a single value rather than an array.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.