CHOOSECOLS stops working in Google Sheets when column numbers are invalid, ranges are incorrect, or formulas reference columns that do not exist.
This usually appears as #VALUE!, missing columns, wrong outputs, or formulas returning unexpected results.
What does CHOOSECOLS do in Google Sheets?
CHOOSECOLS extracts selected columns from a range.
Syntax:
=CHOOSECOLS(array, col_num1, col_num2)
Example:
=CHOOSECOLS(A1:E10,1,3,5)
Result:
- Column 1
- Column 3
- Column 5
from the selected range.
Useful for:
- Dashboards
- Reports
- Dynamic table creation
Why is CHOOSECOLS not working in Google Sheets?
Common causes:
- Invalid column number
- Column number exceeds range width
- Wrong array reference
- Blank or broken source range
- Formula syntax error
- Using row numbers instead of column numbers
- Referencing deleted columns
Why am I getting #VALUE! in CHOOSECOLS?
Most common cause:
- Invalid column number
Wrong:
=CHOOSECOLS(A1:C10,5)
Problem:
Range only has 3 columns
Fix:
=CHOOSECOLS(A1:C10,2)
Always verify selected column exists.
Why is CHOOSECOLS returning the wrong columns?
Usually because:
- Column positions are misunderstood
Example:
=CHOOSECOLS(A1:E10,2)
Returns:
Column B
Not:
Second visible business column
CHOOSECOLS counts physical position inside range.
Example:
A=1
B=2
C=3
Why is CHOOSECOLS returning blank results?
Possible causes:
- Source cells are blank
- Referenced range empty
- Formula depends on another broken formula
Test source first:
=A1:E10
Verify data exists before applying CHOOSECOLS.
Why is CHOOSECOLS failing after copying formula?
Problem:
- Relative range shifts
Wrong:
=CHOOSECOLS(A1:E10,2,3)
Dragged sideways:
- Range changes unexpectedly
Better:
=CHOOSECOLS($A$1:$E$10,2,3)
Use absolute references.
Why is CHOOSECOLS returning an error after deleting columns?
Problem:
Referenced structure changed.
Example:
Original:
A B C D E
Formula:
=CHOOSECOLS(A:E,3)
Delete column B:
Structure shifts.
Result:
- Unexpected output
Fix:
- Recheck column positions.
Why does CHOOSECOLS fail inside ARRAYFORMULA?
Some nested array logic behaves unexpectedly.
Problem:
=ARRAYFORMULA(CHOOSECOLS(A:E,1,3))
Usually unnecessary.
Better:
=CHOOSECOLS(A:E,1,3)
CHOOSECOLS already returns arrays.
Avoid redundant ARRAYFORMULA().
Why is CHOOSECOLS slow on large datasets?
Common cause:
Massive ranges.
Bad:
=CHOOSECOLS(A:Z,1,3,5)
Better:
=CHOOSECOLS(A1:Z5000,1,3,5)
Limit range size for performance.
When should I use CHOOSECOLS?
Best use cases:
- Dynamic reports
- Dashboard inputs
- Selecting only needed columns
- Reordering columns
Example:
=CHOOSECOLS(A:F,4,1,2)
Output order:
D → A → B
Useful for restructuring datasets.
When should I avoid CHOOSECOLS?
Avoid when:
- You need row extraction → use
CHOOSEROWS() - You need conditional filtering → use
FILTER() - You need lookup logic → use
INDEX MATCH
Choose the right function for the task.
CHOOSECOLS vs alternatives
| Formula | Best Use |
|---|---|
| CHOOSECOLS | Select columns |
| CHOOSEROWS | Select rows |
| FILTER | Conditional selection |
| QUERY | SQL-style extraction |
| INDEX | Specific value retrieval |
Best practices for CHOOSECOLS
- Verify column positions carefully
- Use absolute references
- Avoid full-column ranges on large sheets
- Do not wrap unnecessarily in
ARRAYFORMULA() - Recheck formulas after deleting columns
- Test source range first
FAQs
Why is CHOOSECOLS returning #VALUE!?
A selected column number exceeds the range size.
Why is CHOOSECOLS returning wrong columns?
Column positions are counted inside the selected range.
Does CHOOSECOLS work with deleted columns?
Yes, but column positions may shift and break expected outputs.
Why is CHOOSECOLS slow?
Large full-column ranges slow recalculation.
Do I need ARRAYFORMULA() with CHOOSECOLS?
Usually no—CHOOSECOLS() already returns an array.
What is the fastest CHOOSECOLS fix?
Check column numbers first—most errors come from invalid column positions.
Other Google Sheets Fixes: