How does CHOOSEROWS differ from FILTER?

FILTER selects rows that meet a logical condition, while CHOOSEROWS returns rows based on explicit numeric positions. Use FILTER when you want a dynamic subset based on data values; use CHOOSEROWS when you already know the exact row indices you need.

Can CHOOSEROWS return rows in a different order than they appear in the source?

Yes. The function respects the order of the row_num arguments, so you can reorder rows arbitrarily by listing the indices in the desired sequence. This is useful for creating custom reports without rearranging the original table.

What happens if I include duplicate row numbers?

Each occurrence of a row number is processed independently, so duplicate indices cause the same row to appear multiple times in the result. This can be handy for repeating a particular line item, but be aware it increases the size of the spilled array.

Is CHOOSEROWS available in Google Sheets?

No. CHOOSEROWS is part of the dynamic array functions introduced in Excel 365 and is not currently implemented in Google Sheets. Google Sheets users can achieve similar results with INDEX combined with SEQUENCE or FILTER.

CHOOSEROWS function

CHOOSEROWS returns specific rows from a given array based on one or more row numbers, spilling the selected rows into adjacent cells.

=CHOOSEROWS(array, row_num1, ...)

Generate a CHOOSEROWS formula

Describe what you need. The generator will reach for CHOOSEROWS where CHOOSEROWS is the right tool, and tell you when it is not.

Arguments

How CHOOSEROWS reads its arguments

Argument	Required	Description
array	Required	The source range or array to pull rows from; any rectangular range containing values, text, dates, or errors.
row_num1	Required	The first required row index (1-based) indicating which row to return; non-numeric values cause #VALUE!.
...	Repeating	Optional additional row numbers, each 1-based; duplicates are allowed and will repeat rows, out-of-range numbers cause #REF!.

Returns

It returns a spilled array (multiple rows, same columns as the source).

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Pull only perishable items (Tomatoes and Basil)

Ingredient	Supplier	Unit	Qty	Expiry Date
Tomatoes	Fresh Farm	kg	25	2026-10-01
Olive Oil	Mediterranean Imports	L	15	2027-03-15
Parmesan	DairyCo	kg	5	2026-12-20
Basil	Herb Gardens	g	200	2026-09-30
Flour	Baker's Supply	kg	40	2028-01-01

=CHOOSEROWS(A1:E6,2,5)

Result: TomatoesFresh Farmkg252026-10-01BasilHerb Gardensg2002026-09-30

The formula tells CHOOSEROWS to look at the whole stock-take table (A1:E6) and return rows 2 and 5. Row 2 contains the tomatoes record, row 5 contains the basil record, so those two rows spill out in the order requested. All columns are preserved because CHOOSEROWS returns whole rows, not just selected cells.

2. Extract the olive-oil supplier line

Ingredient	Supplier	Unit	Qty	Expiry Date
Tomatoes	Fresh Farm	kg	25	2026-10-01
Olive Oil	Mediterranean Imports	L	15	2027-03-15
Parmesan	DairyCo	kg	5	2026-12-20
Basil	Herb Gardens	g	200	2026-09-30
Flour	Baker's Supply	kg	40	2028-01-01

=CHOOSEROWS(A1:E6,3)

Result: Olive OilMediterranean ImportsL152027-03-15

Only a single row number (3) is supplied, so CHOOSEROWS returns just the third row of the source array. That row holds the olive-oil entry, and because the function always returns a spilled array, the result appears as a one-row range that matches the original column layout.

3. Gather a custom list: Parmesan, Flour, then Tomatoes

Ingredient	Supplier	Unit	Qty	Expiry Date
Tomatoes	Fresh Farm	kg	25	2026-10-01
Olive Oil	Mediterranean Imports	L	15	2027-03-15
Parmesan	DairyCo	kg	5	2026-12-20
Basil	Herb Gardens	g	200	2026-09-30
Flour	Baker's Supply	kg	40	2028-01-01

=CHOOSEROWS(A1:E6,4,6,2)

Result: ParmesanDairyCokg52026-12-20FlourBaker's Supplykg402028-01-01TomatoesFresh Farmkg252026-10-01

Three row numbers are listed: 4 (Parmesan), 6 (Flour) and 2 (Tomatoes). CHOOSEROWS respects the order of the arguments, so the returned array appears in that exact sequence, regardless of the original vertical order. This makes it handy for building ad-hoc checklists.

Common errors

Which CHOOSEROWS error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	A row_num argument is text or a logical value that cannot be coerced to a number.	Replace the non-numeric argument with a proper integer, e.g., change "two" to 2.
#REF!	A supplied row number exceeds the number of rows in the source array.	Ensure every row_num is between 1 and the total row count of the array; adjust 10 to a valid index like 5.
#SPILL!	The destination area already contains data that blocks the spilled result.	Clear the obstructing cells or move the formula to a location with enough empty space.

Tips and when to use something else

List row numbers in ascending order for a compact spill; if you need a filtered view, consider using FILTER instead.
Wrap CHOOSEROWS with INDEX if you only need a single cell from the returned rows.
Combine CHOOSEROWS with SORT to reorder the extracted rows after selection.
When you need to pick specific columns rather than rows, use CHOOSECOLS, which works analogously on the column axis.

Related functions

INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

How does CHOOSEROWS differ from FILTER?: FILTER selects rows that meet a logical condition, while CHOOSEROWS returns rows based on explicit numeric positions. Use FILTER when you want a dynamic subset based on data values; use CHOOSEROWS when you already know the exact row indices you need.
Can CHOOSEROWS return rows in a different order than they appear in the source?: Yes. The function respects the order of the row_num arguments, so you can reorder rows arbitrarily by listing the indices in the desired sequence. This is useful for creating custom reports without rearranging the original table.
What happens if I include duplicate row numbers?: Each occurrence of a row number is processed independently, so duplicate indices cause the same row to appear multiple times in the result. This can be handy for repeating a particular line item, but be aware it increases the size of the spilled array.
Is CHOOSEROWS available in Google Sheets?: No. CHOOSEROWS is part of the dynamic array functions introduced in Excel 365 and is not currently implemented in Google Sheets. Google Sheets users can achieve similar results with INDEX combined with SEQUENCE or FILTER.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17