The CHOOSE function stops working in Google Sheets when index numbers are invalid, values are referenced incorrectly, or formulas return unexpected outputs.
This usually appears as #VALUE!, wrong selections, blanks, or formulas returning the wrong item.
What does the CHOOSE function do in Google Sheets?
CHOOSE returns a value based on a position number.
Syntax:
=CHOOSE(index_num, value1, value2, value3)
Example:
=CHOOSE(2,"Apple","Banana","Orange")
Result:
Banana
Because:
2 = second item
Why is CHOOSE not working in Google Sheets?
Common causes:
- Invalid index number
- Index larger than available options
- Wrong formula structure
- Text used instead of numeric index
- Missing commas or syntax errors
- Referencing wrong cells
- Blank index cells
Why am I getting #VALUE! in CHOOSE?
Most common cause:
- Invalid index number
Wrong:
=CHOOSE("A","Apple","Banana")
Problem:
- Index must be numeric
Correct:
=CHOOSE(1,"Apple","Banana")
Or reference a numeric cell:
=CHOOSE(A2,"Apple","Banana")
Where:
A2 = 1 or 2
Why is CHOOSE returning the wrong result?
Usually because:
- Wrong index selected
Example:
=CHOOSE(3,"Low","Medium","High")
Result:
High
Check numbering carefully:
1 = first item
2 = second item
3 = third item
CHOOSE starts from 1, not 0.
Why is CHOOSE returning blank?
Possible causes:
- Referenced cell is blank
- Formula logic incomplete
Example:
=CHOOSE(A2,"Yes","No")
If:
A2 = blank
Result:
- Error or blank behavior
Fix:
=IF(A2="","",CHOOSE(A2,"Yes","No"))
Why is CHOOSE showing an error for large numbers?
Problem:
- Index exceeds available values
Wrong:
=CHOOSE(5,"A","B","C")
Only 3 values exist.
Fix:
- Keep index within range
Example:
1–3 only
Why is CHOOSE failing with text input?
Problem:
=CHOOSE(A2,"Small","Medium","Large")
If:
A2 = Medium
Fails because:
CHOOSE expects:
1
2
3
Fix:
Convert logic:
=MATCH(A2,{"Small","Medium","Large"},0)
Or use IFS() for text logic.
Why does CHOOSE break after copying formulas?
Problem:
Referenced cells shift.
Wrong:
=CHOOSE(A2,B1,C1,D1)
Dragged formula:
- References move unexpectedly
Better:
=CHOOSE(A2,$B$1,$C$1,$D$1)
Use absolute references.
Why is CHOOSE hard to maintain?
Large formulas become messy.
Bad:
=CHOOSE(A2,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug")
Hard to scale.
Better:
Use lookup table:
=XLOOKUP(A2,A:A,B:B)
or
=INDEX(B:B,MATCH(A2,A:A,0))
Better for larger datasets.
When should I use CHOOSE?
Good use cases:
- Small dropdown logic
- Dynamic formula switching
- Simple category mapping
- Small dashboards
Example:
=CHOOSE(A2,"Sales","Finance","HR")
Simple and readable.
When should I avoid CHOOSE?
Avoid for:
- Large lookup tables
- Complex logic
- Dynamic databases
Better options:
INDEX MATCHXLOOKUPIFS()SWITCH()
CHOOSE vs alternatives
| Formula | Best Use |
|---|---|
| CHOOSE | Small fixed options |
| IFS | Conditional logic |
| SWITCH | Text-based mapping |
| INDEX MATCH | Large lookup logic |
| XLOOKUP | Flexible lookup |
Best practices for CHOOSE
- Keep index numbers valid
- Use numeric inputs only
- Avoid giant CHOOSE formulas
- Use absolute references
- Handle blanks explicitly
- Switch to lookup tables for scale
FAQs
Why is CHOOSE returning #VALUE!?
The index number is invalid or non-numeric.
Why is CHOOSE returning the wrong value?
The wrong index number is being used.
Does CHOOSE start from 0 or 1?
CHOOSE() starts from 1.
Why is CHOOSE failing with text?
The index must be numeric, not text.
What is better than CHOOSE for large datasets?
XLOOKUP or INDEX MATCH are more scalable.
What is the fastest CHOOSE fix?
Check the index number first—most errors come from invalid positions.
Other Google Sheets Fixes: