CHOOSE with an out-of-range index: #NUM! in Google Sheets, #VALUE! in Excel and LibreOffice
CHOOSE picks the n-th item from a list: =CHOOSE(2,"a","b","c") is
"b". Every engine agrees on that. They disagree on what happens when the index runs
off the end of the list — =CHOOSE(5,"a","b","c"), where only three values are
supplied. Excel documents a #VALUE! error there, and LibreOffice returns exactly that,
but Google Sheets returns #NUM! instead. Same out-of-range call, two different error
codes.
The surprise
The error is not the surprise — both apps correctly refuse an out-of-range index. The
surprise is which error: Excel and LibreOffice raise #VALUE!, Google Sheets
raises #NUM!. Any formula that inspects the specific error code (with
ERROR.TYPE, or a test for one particular error) will read a different value in each
app.
A minimal example
| Formula | Excel, desktop (documented) | Google Sheets (executed 2026-08-29) | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =CHOOSE(5,"a","b","c") | #VALUE! | #NUM! | #VALUE! |
| =CHOOSE(2,"a","b","c") | b | b | b |
| =CHOOSE(2.9,"a","b","c") | b | b | b |
#VALUE! is the documented-expected value in our test corpus; the Google Sheets and
LibreOffice columns are what our harness computed by recalculating the same workbook — Google
Sheets via Drive import on 2026-08-29, LibreOffice in Calc 25.8.7.3, and the out-of-range result is
identical in all four LibreOffice releases we test (24.2, 24.8, 25.2, 25.8). The in-range and
fractional-index rows match everywhere: 2.9 is truncated to 2 (not
rounded to 3), so only the out-of-range error code forks.
Why it happens
Microsoft’s reference is explicit: “If index_num is less than 1 or greater than the
number of the last value in the list, CHOOSE returns the #VALUE! error value.”
LibreOffice follows that letter for letter. Google Sheets rejects the same input but classifies it
as a numeric-domain problem — index 5 has no fifth argument to point at — and returns
#NUM!. Both are refusing the out-of-range index; they just file it under different
error categories. See Microsoft’s
CHOOSE function reference.
How to migrate safely
The value you get back is an error in every app, so a formula that just displays the result is
unaffected. The break happens when something downstream branches on the kind of error.
=ERROR.TYPE(CHOOSE(5,"a","b","c")) is 3 in Excel and LibreOffice
(#VALUE!) but 6 in Google Sheets (#NUM!), so a lookup keyed
on that number silently changes branch.
Do not test for a specific error code. Either bound-check the index before calling CHOOSE:
=IF(OR(n<1,n>3),"out of range",CHOOSE(n,"a","b","c"))
or normalise any error to one fallback with a code-agnostic wrapper:
=IFERROR(CHOOSE(n,"a","b","c"),"out of range")
IFERROR catches #VALUE! and #NUM! alike, so it produces the
same result in every engine — which is exactly why it is safer here than
=IF(ERROR.TYPE(…)=3,…), a test that only ever matches in Excel and
LibreOffice.
Check before you migrate
A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.