How to Use FILTER and CHOOSECOLS to Transfer Excel Data Between Sheets
Question details
The user wants to extract specific columns from a master data table and transfer matching records to separate worksheets using dynamic array functions.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting subsets of data from a main forms data table into specific department or district sheets based on matching criteria.
- Observed behavior
- The user needs a working formula combination and guidance on avoiding spill errors caused by Tables in the destination range.
Ensure that your destination worksheet area is formatted as a normal range rather than a Table, as dynamic array formulas cannot spill data into Tables. Verify that your source data range is clearly defined.
Use FILTER, CHOOSECOLS, and XMATCH to Extract Data
Combine dynamic array functions to dynamically pull specific columns and filter them by multiple criteria onto a new sheet.
The FILTER function extracts rows based on conditions, while CHOOSECOLS returns only the specified columns. XMATCH can dynamically find column positions based on headers. The destination cells must be standard ranges to allow the formula to spill automatically into adjacent cells.
Navigate to the destination worksheet. If your output area is currently an Excel Table, click anywhere inside it, go to the Table Tools tab, and click 'Convert to Range'.
In your destination sheet (e.g., cell A8), enter the combined formula: =FILTER(CHOOSECOLS(FormsData, XMATCH(A1, FormsData[#Headers]), 14, 15, 16, 17, 18), (FormsData[Job Code]=C1)*(FormsData[District]=A1), "None found")
Modify the column index numbers (such as 14, 15, 16) in the CHOOSECOLS segment to match the exact column positions you want to extract from your source data.
To prevent error codes when a department or header does not exist, wrap your final formula in an IFERROR function by structuring it as =IFERROR([your_formula], "Data Missing").
Create a Single Interactive Dashboard
Instead of creating multiple separate output sheets, use a single dashboard with a drop-down list to dynamically switch between departments.
Manage Dynamic Arrays Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, CHOOSECOLS, and XMATCH, making complex data extraction and transfers simple and efficient.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your master data.
- 2. Select target cell: Navigate to the blank destination sheet and click on the cell where you want the extracted data to start.
- 3. Apply dynamic array: Type your =FILTER(CHOOSECOLS(...)) formula and press Enter. WPS Spreadsheet will automatically spill the results into the adjacent empty cells.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the FILTER function?
A #SPILL! error occurs when the dynamic array's destination range is blocked. Ensure the output area is not formatted as a Table, and clear any merged cells, text, or existing data in the cells where the formula needs to expand.
Can I extract columns that are not adjacent to each other?
Yes, the CHOOSECOLS function allows you to specify multiple non-adjacent column index numbers separated by commas. For example, CHOOSECOLS(array, 1, 4, 7) will extract the first, fourth, and seventh columns.
How do I handle missing data without showing an error code?
You can wrap your main formula inside the IFERROR function to handle general errors. Additionally, the FILTER function itself has a built-in [if_empty] argument that allows you to display custom text (like "None found") when no records match your filter criteria.
