How to Return Multiple Matching Rows and Non-Adjacent Columns in Excel
Question details
The user needs a method to extract specific non-adjacent columns across multiple rows based on whether data in one column matches a predefined list of values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Returning values from columns A and C specifically when the corresponding value in column B matches any value listed in column N.
- Observed behavior
- Requires an approach that effectively filters out the unwanted rows and correctly retrieves only the desired disjointed columns, scalable to thousands of rows.
Ensure you are using a modern version of Excel (Microsoft 365 or Excel 2021) or WPS Office that supports dynamic array functions like FILTER and CHOOSECOLS.
Use FILTER and CHOOSECOLS Dynamic Array Functions
This is the most efficient method for modern spreadsheet applications, allowing you to filter rows based on a list of conditions and pull out specific non-adjacent columns simultaneously.
By nesting the FILTER function inside the CHOOSECOLS function, you can dynamically extract exact columns from a dataset while simultaneously applying matching criteria using COUNTIF or MATCH.
Determine your source data range (e.g., columns A through C) and your criteria list (e.g., the lookup values located in column N).
Select the target cell where you want the results to begin and type the formula: =CHOOSECOLS(FILTER(A:C, COUNTIF(N:N, B:B)>0), 1, 3).
Press Enter. The dynamic array will automatically spill the filtered results into the adjacent rows and columns, displaying only columns A (1st column) and C (3rd column) where a match was found.
Use Advanced Filter for Older Excel Versions
Ideal for older versions of Excel that do not support dynamic arrays, this built-in tool allows you to extract specific columns to a new location without complex formulas.
Filter and Extract Data Easily in WPS Office
WPS Spreadsheet fully supports powerful dynamic array formulas, making it incredibly simple to filter multiple rows and extract non-adjacent columns without complex workarounds.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data.
- 2. Select output cell: Click on the destination cell where you want the top-left of the filtered data to appear.
- 3. Enter the formula: Type =CHOOSECOLS(FILTER(A1:C1000, COUNTIF(N1:N10, B1:B1000)>0), 1, 3) adjusting the ranges as needed.
- 4. Press Enter: Press Enter to instantly display the extracted non-adjacent columns and matching rows.
Frequently Asked Questions
Can I use VLOOKUP or XLOOKUP to return multiple matching rows?
No, standard lookup functions like VLOOKUP and XLOOKUP are designed to return only the first matching instance they encounter. To return multiple matching rows, you should use the FILTER function.
Why is my FILTER formula returning a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no matches for your criteria. You can easily fix this by adding an [if_empty] argument at the end of your FILTER function, such as =FILTER(A:C, B:B=N1, "No match").
How do I return more than two non-adjacent columns using CHOOSECOLS?
You can easily expand the CHOOSECOLS function by adding more column index numbers separated by commas. For example, =CHOOSECOLS(array, 1, 3, 5, 7) will return the 1st, 3rd, 5th, and 7th columns of your filtered data.
