Why INDEX with an Array of Column Numbers Returns One Row in Excel
Question details
The user is attempting to retrieve multiple specific columns using the INDEX function with an array of column numbers, but the formula only outputs the first row instead of the entire array.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting non-adjacent columns from a dataset using an array constant like {1,3,5} inside the INDEX function.
- Observed behavior
- While INDEX(range,0,0) successfully returns the full dataset, INDEX(range,0,{1,3,5}) only returns the first row of the selected columns.
Verify your Excel version to determine whether your software supports dynamic arrays (which allow formulas to spill naturally) or if you need to manually evaluate legacy array formulas.
Use the CHOOSECOLS Function (Modern Excel)
In current Excel versions (Microsoft 365 and Excel 2021+), using a dynamic-array function like CHOOSECOLS is the easiest and most reliable way to return multiple specific columns without array evaluation errors.
The CHOOSECOLS function is specifically designed to return specified columns from an array or range. It completely bypasses the complex array and reference behaviors of the traditional INDEX function.
Click on an empty cell where you want the extracted columns to start. Ensure there is enough empty space below and to the right for the data to spill.
Type the formula =CHOOSECOLS(A1:E10, 1, 3, 5). Replace 'A1:E10' with your actual data range, and '1, 3, 5' with the specific column numbers you wish to extract.
Press Enter. The formula will automatically spill all rows for the specified non-adjacent columns.
Enter INDEX as a Legacy Array Formula
If you are using an older version of Excel that does not support dynamic arrays, you must explicitly evaluate the INDEX formula as a traditional array formula across a pre-selected range.
Extract Columns Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet fully supports dynamic arrays and modern functions like CHOOSECOLS. You can quickly extract non-adjacent columns and all their rows without worrying about legacy array evaluation issues.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Select your output cell: Click on the empty cell where the extracted columns should start.
- 3. Apply the function: Type =CHOOSECOLS(range, col_num1, col_num2) and press Enter.
- 4. View the spilled array: The software will automatically spill the full rows for the specified columns instantly.
Frequently Asked Questions
Why does INDEX(range, 0, 0) return the full dataset while INDEX(range, 0, {1,3}) fails to return all rows?
Using 0 for both row and column arguments in INDEX explicitly tells Excel to return all rows and all columns. However, when passing an array constant like {1,3} to the column argument, older Excel versions default to implicit intersection, returning only the first row unless explicitly evaluated as a multi-cell array formula.
What dimensions do I need to select for the legacy array formula?
When using Ctrl+Shift+Enter, you must highlight a destination range that has the exact same number of rows as your original dataset, and the exact number of columns matching your array constant (e.g., exactly 3 columns if you used {1,3,5}).
Are dynamic arrays like CHOOSECOLS available in older versions of Excel?
No, dynamic arrays and spilling behaviors are only available in Microsoft 365, Excel 2021, and newer versions. For older standalone versions (like Excel 2016 or 2019), you must rely on legacy Ctrl+Shift+Enter array formulas.
