How to Use an Excel Formula in WPS Office to Return Rows Matching One Value and Excluding Another
Question details
The user needs an Excel-compatible spreadsheet formula that returns rows from one column where a second column equals a specified value, such as Blue, while excluding rows where another column contains excluded text, such as Red.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is filtering a dataset with formulas and needs dynamic row extraction without using manual filters.
- Observed behavior
- The goal is a formula-driven list that includes matching rows, such as Blue, and automatically skips records containing Red anywhere in the excluded column.
Open your workbook in WPS Spreadsheets and confirm that the return, condition, and exclusion columns use consistently aligned ranges. If your version requires legacy array entry, be ready to press Ctrl+Shift+Enter after typing the formula.
Return matching rows with an IFERROR, INDEX, SMALL, and SEARCH array formula
Use this method when you need a traditional array formula that returns Blue rows and excludes any row where the excluded column contains Red.
This formula combines SEARCH with ISERROR to skip excluded text and uses INDEX with SMALL to return the remaining matching rows in order.
Click the first cell where you want the extracted row values to appear.
Type =IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5="Blue",IF(ISERROR(SEARCH("Red",$C$2:$C$5)),ROW($A$2:$A$5)-ROW($A$1))),ROW(1:1)),1),"") and adjust the ranges to match your worksheet.
Press Enter if your WPS Spreadsheets version supports dynamic arrays. Otherwise press Ctrl+Shift+Enter to confirm it as a legacy array formula.
Drag the fill handle down from the formula cell until blank cells appear, which indicates that no more rows match the conditions.
Use This Excel-Compatible Array Formula Directly in WPS Spreadsheets
You can complete this task directly in WPS Spreadsheets by entering the same array formula. WPS Spreadsheets supports Excel-compatible functions such as INDEX, SMALL, SEARCH, and IFERROR, so your formula works without changing syntax.
- 1. Open the workbook: Open your .xlsx or .xls workbook in WPS Spreadsheets.
- 2. Select the first output cell: Click the cell where the first returned row value should appear.
- 3. Enter the array formula: Type or paste the IFERROR, INDEX, SMALL, and SEARCH formula and adjust the ranges to your dataset.
- 4. Confirm and fill down: Press Enter or Ctrl+Shift+Enter as needed, then drag the fill handle down to list all matching rows.
Frequently Asked Questions
Why does my formula return blanks after the first matching row?
The formula uses SMALL with ROW(1:1), and the row number increments as you fill down. When the increment exceeds the number of matching rows, IFERROR returns blank cells, which means there are no more matches to display.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
It depends on your WPS Spreadsheets version. If dynamic arrays are supported, press Enter after typing the formula. If not, select the formula cell, press F2, and then press Ctrl+Shift+Enter to enter it as a legacy array formula.
Can I use this formula with different worksheet ranges?
Yes. Replace $A$2:$A$5, $B$2:$B$5, and $C$2:$C$5 with your actual ranges, keeping the same starting and ending rows across all columns so the formula aligns correctly.
What is the difference between SEARCH and FIND in this formula?
SEARCH is case-insensitive, so it treats Red, red, and RED as excluded text. Use FIND instead of SEARCH if you need case-sensitive matching.
Why use ISERROR(SEARCH(...)) instead of a simple not-equal-to-Red condition?
ISERROR(SEARCH(...)) excludes any cell that contains Red anywhere within the text, whereas a direct comparison like "<>Red" only excludes cells that are exactly equal to Red.
