How to Select Non-Contiguous Columns in Excel Structured References
Question details
The user needs to reference or select two or more non-adjacent columns from an Excel table using structured reference formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to extract specific, separated columns from a named data table to display them side-by-side in a new location.
- Observed behavior
- Entering the syntax like Temp[[A],[C]] results in a formula error, as standard structured references do not natively support selecting non-contiguous columns.
Ensure your data range is formatted as a formal Table and assigned a specific name (e.g., 'Temp') via the Table Design tab before you begin writing structured reference formulas.
Use the CHOOSE Function with an Array Constant
By wrapping structured references within the CHOOSE function and providing an array constant, you can effectively merge non-contiguous table columns side-by-side.
The standard structured reference syntax cannot handle non-adjacent columns in a single bracket set. The CHOOSE function acts as an intermediary, pulling the exact columns you specify and merging them into one output array.
Verify the exact name of your data table (for example, 'Temp') and note the specific headers of the non-adjacent columns you wish to extract (for example, 'A' and 'C').
Click on an empty destination cell where you want the extracted columns to appear, and type =CHOOSE( to begin the formula.
Type the array constant {1,2}, immediately after the opening parenthesis. This tells the spreadsheet program you want to output two separate columns.
Input the individual structured references for each column separated by commas. Your formula should now look like: =CHOOSE({1,2}, Temp[A], Temp[C]).
Press Enter to execute the formula. The data from the two separated table columns will dynamically spill into the adjacent cells side-by-side.
Use the HSTACK Function (For Newer Versions)
If you are using a modern spreadsheet version with dynamic arrays, the HSTACK function provides a simpler, cleaner alternative to combine columns horizontally.
Easily Manage Table References in WPS Office
WPS Spreadsheet provides robust support for structured table references, dynamic arrays, and advanced functions like CHOOSE and HSTACK. You can handle complex data extraction tasks seamlessly without worrying about compatibility issues.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the named data table.
- 2. Select your destination cell: Click on the specific cell where you want to output the extracted non-adjacent columns.
- 3. Apply the structured formula: Type =CHOOSE({1,2}, TableName[Column1], TableName[Column2]) and press Enter. Your non-contiguous columns will display instantly.
Frequently Asked Questions
Why doesn't the syntax TableName[[Col1],[Col2]] work in Excel?
Excel's standard structured reference syntax only permits a colon operator to select a contiguous range of columns (e.g., TableName[[Col1]:[Col2]]). Attempting to separate individual column references with a comma inside a single outer bracket is not recognized as valid syntax.
Can I combine more than two non-adjacent columns?
Yes. To extract three or more columns using the CHOOSE function, expand your array constant and add the subsequent references. For instance, to pull three columns, your formula would look like =CHOOSE({1,2,3}, Temp[A], Temp[C], Temp[E]).
Does the CHOOSE method work with regular cell ranges instead of tables?
Absolutely. The CHOOSE formula handles standard A1-style ranges just as effectively as structured references. You can type =CHOOSE({1,2}, A2:A10, C2:C10) to merge non-adjacent data ranges that are outside of a formalized table.
