How to Filter WPS Office Spreadsheets Rows Where the Same Value Appears in Two Columns
Question details
Filter a spreadsheet table so only rows where the same value, such as XYZ, appears in both the purchaser and seller columns are returned.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table with purchaser and seller columns and needs to isolate rows based on both columns matching a specific value.
- Observed behavior
- The goal is to return rows only when both columns contain the target value, rather than rows where only one column matches.
Open the workbook in WPS Office Spreadsheets and verify that the purchaser and seller columns use the exact header names you will enter in the criteria range or formula.
Use Advanced Filter with a criteria range for row filtering
Best for one-time filtering in WPS Office Spreadsheets; it returns only rows where the same value appears in both the purchaser and seller columns.
Advanced Filter treats criteria on the same row as AND conditions. When you place XYZ under both matching headers, WPS Spreadsheets returns rows that satisfy both matches.
Copy the purchaser and seller headers from the source table to a blank area, then type XYZ in the cell directly below each copied header.
Click any cell inside the source table that contains the purchaser and seller columns.
Go to the Data tab and click Advanced Filter or Advanced to open the dialog.
Set the list range to include the source table headers and rows, set the criteria range to the copied headers and the two XYZ cells, then choose Filter the list in-place or Copy to another location.
Click OK. WPS Office Spreadsheets displays only the rows where both the purchaser and seller columns contain XYZ.
Use the FILTER formula for a dynamic result in supported versions
Use this method when you need the filtered rows to update automatically as the source data changes and your spreadsheet application supports the FILTER function.
Filter matching rows in two columns with WPS Office Spreadsheets
Use the Advanced Filter in WPS Office Spreadsheets to set a criteria range under identical purchaser and seller headers, then run a focused AND filter without changing the original data.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the Excel-compatible workbook that contains the purchaser and seller columns.
- 2. Set up the criteria range: Copy the purchaser and seller headers to a blank area and enter the target value under each header.
- 3. Run Advanced Filter: Select the source table, click Data > Advanced Filter, and set the list range and criteria range.
- 4. Apply and review: Click OK to filter in place or copy the results, then confirm only rows with the value in both columns are shown.
Frequently Asked Questions
How do I return rows where XYZ appears in either the purchaser or seller column instead of both?
Place the two XYZ values on separate rows below the same copied header in the criteria range, or use the formula =FILTER(Table1,(Table1[purchaser]="XYZ")+(Table1[seller]="XYZ")).
Do the criteria range headers have to match the source headings exactly?
Yes. The criteria headers must match the source table headers exactly so WPS Office Spreadsheets can map the conditions to the correct purchaser and seller columns.
Can I show the results in place instead of copying them to another location?
Yes. In the Advanced Filter dialog, choose Filter the list in-place to hide the non-matching rows, or choose Copy to another location and set a destination range.
Why does the FILTER formula return #NAME? or #SPILL!?
#NAME? usually means the FILTER function is not available in your spreadsheet version. #SPILL! means the spill range is blocked by existing data; use a clear area or switch to Advanced Filter.
