Extract Excel Text Values Using One or Two Criteria in WPS Spreadsheets
Question details
The user needs to extract matching goal text values from a database when filtering by a selected office, a selected priority type, or both criteria in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a goal database and wants one dynamic formula to return the correct rows whenever the criteria cells contain Office only, Priority only, or both Office and Priority.
- Observed behavior
- The expected behavior is a spill or array result that lists all matching goals for the current criteria without manual filtering.
Arrange the source data in separate columns for Goals, Office, and Priority, and confirm that your WPS Spreadsheets version supports the FILTER function. Keep the formula output area empty so the extracted list can spill.
Use a nested IF with FILTER to return matching goal values
Use this method when you need one dynamic formula that handles Office only, Priority only, or both Office and Priority selections.
The formula tells FILTER which rows to include based on the current criteria cells. The nested IF checks the three required cases: only Office selected, only Priority selected, or both selected. If neither is selected, the example returns all rows; adjust that branch if you prefer an empty result.
Place Goals in A2:A1000, Office in B2:B1000, and Priority in C2:C1000. Enter the Office criterion in cell E2 and the Priority criterion in cell F2.
In an empty cell, enter: =FILTER(A2:A1000, IF(AND(E2="",F2=""), ROW(A2:A1000)>0, IF(F2="", B2:B1000=E2, IF(E2="", C2:C1000=F2, (B2:B1000=E2)*(C2:C1000=F2)))), "No matching goals"). Press Enter in dynamic array versions of WPS Spreadsheets; otherwise confirm with Ctrl+Shift+Enter.
Change A2:A1000, B2:B1000, and C2:C1000 to match your worksheet. Test with E2 alone, F2 alone, and both cells filled, and increase the row limit as needed.
Extract matching text values by one or two criteria in WPS Spreadsheets
WPS Spreadsheets supports the FILTER function and nested IF logic, so you can create a dynamic criteria-based extraction while keeping your current workbook structure and formulas.
- 1. Open the workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel workbook that contains your goal database.
- 2. Apply the FILTER and nested IF formula: Use the formula described above in an empty cell, replacing the ranges with your actual goal, office, and priority columns.
- 3. Test Office-only, Priority-only, and combined criteria: Try each criteria combination by changing the selection cells, and confirm the extracted list updates correctly.
- 4. Save the workbook: Save the file as .xlsx to keep compatibility with Microsoft Excel and WPS Spreadsheets.
Frequently Asked Questions
Does this formula work in WPS Spreadsheets if my source data has more than 1,000 rows?
Yes, increase the row numbers in the formula ranges—for example, change A2:A1000, B2:B1000, and C2:C1000 to A2:A5000, B2:B5000, and C2:C5000, or use larger ranges that cover your full dataset.
What happens when the Office and Priority criteria cells are both blank?
The example nested IF returns all rows when both cells are blank. If you want blank criteria to return no results instead, replace the ROW(A2:A1000)>0 branch with a condition that returns FALSE for all rows.
Can I use dropdown lists for the Office and Priority criteria cells?
Yes. Select E2, go to Data > Validation, choose List, and enter the office values. Repeat for F2 with the priority values so users can select criteria without typing.
Why does my formula return a #SPILL! error?
The extracted list is trying to occupy cells that already contain data. Move the formula to an empty area with enough contiguous blank cells, or reduce the source range so the returned list fits.
Can I extend this approach to filter by more than two criteria?
Yes, add another IF level or multiply additional Boolean conditions, such as (D2:D1000=G2), when all selected conditions must be true.
