How to Return Rows Matching Values in Either of Two Columns in Excel
Question details
The user needs to extract rows from a dataset where a specific value matches either of two designated columns, and then remove any duplicate results.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing a list of lookup numbers against two distinct columns in a data table to extract matching records.
- Observed behavior
- The goal is to return a deduplicated list of entire rows where the specified condition is met in at least one of the two target columns.
Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions like FILTER and UNIQUE (typically Microsoft 365 or newer versions).
Use the FILTER and UNIQUE Functions
Combine the FILTER function with OR logic (addition) to extract rows matching conditions in either column, wrapped in UNIQUE to deduplicate.
Dynamic array formulas such as FILTER allow you to extract data based on multiple criteria without complex VBA macros.
To check two different columns simultaneously, you can use the addition operator (+) inside the FILTER condition, which acts as a logical OR.
Click on an empty cell where you want the filtered results to begin spilling. Make sure there is enough empty space below and to the right.
Type the formula =FILTER(A2:D100, (B2:B100=TargetValue) + (C2:C100=TargetValue), "No match"), adjusting ranges and target values accordingly.
To ensure no duplicate rows are returned from the dataset, wrap the function like this: =UNIQUE(FILTER(A2:D100, (B2:B100=TargetValue) + (C2:C100=TargetValue), "No match")).
Hit Enter, and the formula will automatically spill all the matching unique rows into the adjacent cells.
Use the CompareSheets Add-in
Use a third-party add-in like CompareSheets to compare lists and extract unique or matching rows without writing formulas.
Easily Filter and Extract Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including filtering and logic checks, making it simple to process and extract your data quickly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet module.
- 2. Input the Data: Ensure your dataset and the target values are organized in clear, distinct columns.
- 3. Apply the Formula: Use the same =UNIQUE(FILTER(...)) syntax right within WPS Spreadsheet to instantly extract your desired rows.
- 4. Save and Share: Save your document in .xlsx format for perfect compatibility when sharing with Microsoft Excel users.
Frequently Asked Questions
What does the plus sign (+) mean in a FILTER formula?
In array formulas, the plus sign (+) acts as a logical OR operator. It tells the software to return a row if the condition on the left side OR the right side of the plus sign evaluates to TRUE.
Why am I getting a #CALC! error when using the FILTER function?
The #CALC! error usually occurs if the FILTER function evaluates to an empty array (meaning no rows matched your criteria) and you haven't provided an 'if_empty' argument. Try adding a fallback string like "No matches" at the end of the formula parameters.
How do I filter rows using more than two columns?
You can expand the OR logic by adding more conditions inside the FILTER function's include argument. For example: =FILTER(Array, (Col1=Value) + (Col2=Value) + (Col3=Value)).
Does WPS Spreadsheet support the FILTER and UNIQUE functions?
Yes, the latest versions of WPS Office include full support for dynamic array functions, allowing you to use FILTER, UNIQUE, and SORT to manage data effectively.
