Zoho Sheet CHOOSECOLS: Select Columns and Preserve Row Alignment
Select columns in Zoho Sheet with relative indices, complete spill examples, quantity-price checks and a nested positional sample.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
=CHOOSECOLS(A2:D9;1;3) returns the first and third columns of that source rectangle. In Zoho Sheet, the column numbers are relative to the supplied array. Selecting fields this way keeps their rows aligned, without deleting the other source columns.
Availability and version history
- Introduced: Named in the fifteen-function launch linked from Zoho’s March 2023 release history. No numbered cloud release is given.
- Availability: Documented in Zoho Sheet’s current rolling web-service help; no final supported-version endpoint is established.
- Scope: Mobile, offline and imported-workbook parity remain separately unverified.
- Checked: October 14, 2026, against the official CHOOSECOLS entry. Coordinate and arithmetic examples were independently calculated; no native Zoho Sheet execution was performed.
Syntax and relative column numbers
CHOOSECOLS(array; column; [column1]; ...)
The source range and one column index are required; more column indices are optional. The official entry demonstrates selecting columns 1 and 3 together, or column 2 alone. Brackets and ellipses describe optional syntax; do not type them in these formulas.
CHOOSEROWS selects rows instead. These are distinct operations, even though both return part of an array.
Build three views of eight order records
Create OrderViews, reserving A1:T12. Enter the following complete fictitious dataset in A1:D9, with text in A:B and numbers in C:D. Keep that rectangle unchanged while adding views.
| Row | A: ID | B: Region | C: Units | D: Unit price |
|---|---|---|---|---|
| 2 | P1 | North | 3 | 12 |
| 3 | P2 | South | 5 | 8 |
| 4 | P3 | North | 2 | 20 |
| 5 | P4 | West | 7 | 5 |
| 6 | P5 | South | 4 | 11 |
| 7 | P6 | West | 6 | 6 |
| 8 | P7 | North | 1 | 30 |
| 9 | P8 | South | 8 | 4 |
Leave F2:G9, I2:J9, L2:M9 and R2:R9 empty. Put ID and Units in F1:G1, Region and Unit price in I1:J1, and Units and Unit price in L1:M1. Then enter:
F2: =CHOOSECOLS(A2:D9;1;3)
I2: =CHOOSECOLS(A2:D9;2;4)
L2: =CHOOSECOLS(A2:D9;3;4)
F2:G9 is P1,3 / P2,5 / P3,2 / P4,7 / P5,4 / P6,6 / P7,1 / P8,8. I:J contains the corresponding region and price from each original row. L:M contains the two numeric fields, still paired by record.
Zoho’s dynamic-array instructions specify one formula in the anchor cell followed by Enter. Leave its complete output rectangle empty; do not fill the same formula across the intended result. An obstructing value produces the documented #SPILL! error. Edit the source or anchor rather than treating individual result cells as new inputs.
Put Amount in O1, =L2*M2 in O2 and fill through O9. The expected amounts are 36,40,40,35,44,36,30,32. T2 =SUM(O2:O9) gives 293, and T3 =SUM(G2:G9) gives 36 units. Multiplying each selected quantity by its own price matters; selecting the right fields is not enough if their row relationships are later changed independently.
The views omit the source header because A2:D9 excludes row 1. Output headings were entered manually outside the spill rectangles.
An offset source changes what index 2 means
In R2 enter =CHOOSECOLS(B2:D9;2). The source now begins at worksheet column B, so its second column is worksheet C, containing Units. R2:R9 is 3,5,2,7,4,6,1,8. T4 =SUM(R2:R9) is 36.
Index 2 does not always mean worksheet column B. Count within the exact source range, including any columns you decided to retain or exclude from that range. A reference that starts one column later can remain syntactically valid while selecting a different field.
Copy OrderViews as ChangedUnits. Change only C5, the P4 quantity, from 7 to 10. G5, L5 and R5 become 10; O5 becomes 50. T2 becomes 308, while T3 and T4 become 39. I:J and the prices in M stay unchanged. The function has preserved the row mapping as the data changed.
Advanced: select fields and then a positional sample
Create SampledOrders, reserving A1:M12. Enter the same A1:D9 dataset again. Leave F2:H4 empty and enter this formula in F2:
=CHOOSEROWS(CHOOSECOLS(A2:D9;1;3;4);2;4;6)
The inner function retains ID, Units and Unit price; the outer one selects source-array rows 2, 4 and 6. Expected F2:H4 is:
| Output row | F: ID | G: Units | H: Unit price |
|---|---|---|---|
| 2 | P2 | 5 | 8 |
| 3 | P4 | 7 | 5 |
| 4 | P6 | 6 | 6 |
I2 =G2*H2 filled through I4 gives 40,35,36. K2 =SUM(G2:G4) is 18, and K3 =SUM(I2:I4) is 111. These totals describe the selected rows only, not all eight records. SUM adds the explicit resulting numbers.
Duplicate SampledOrders as ReplacedRecord. Replace A3:D3 with P9, North, 9, 2, keeping the other seven rows unchanged. The first selected row becomes P9,9,2; its amount becomes 18. K2 becomes 22, and K3 becomes 89.
The selection follows position, so it now contains the North record placed in that slot. It did not filter for a region, freeze the former P2 record or preserve an earlier business category. Keep identifiers in the projected result when a reader needs to understand which records contributed.
Documented errors and unverified boundaries
The function-specific entry lists #VALUE! for index zero, a negative index or an index larger than the corresponding source dimension. In particular, do not import another program’s negative-index-from-the-end convention. The entry also lists invalid argument types, invalid references and #NUM! for an array that is too large, without quantifying that size limit.
These exercises use ascending, distinct, positive integer indices and explicit text/numeric cells. The short entry does not settle duplicate indices, reverse-order selectors, fractional indices, formula-empty strings, embedded errors or every import conversion. Keep those cases separate from the documented selection pattern. A valid index selects a position; it does not check the record’s meaning or whether the chosen sample represents the full dataset.