Create an Automatic Multi-Criteria Name List in WPS Spreadsheets
Question details
Create a dynamic list of names from column B when values in columns E and S meet two criteria entered on a separate worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants a results list that automatically refreshes whenever either criterion changes.
- Observed behavior
- The desired result is a spilled list containing every matching name, or a message when no matching records are found.
Confirm that the source data has headers and that the ranges in columns B, E, and S start and end on the same rows. Leave the cells below the output formula empty so the results can spill into them.
Use FILTER to return names matching two criteria
Use this method in current WPS Spreadsheets versions to generate an automatically updating list without helper columns.
The FILTER function tests each row against both conditions and returns the corresponding names from column B. Multiplying the two tests applies an AND condition, so both criteria must match.
On the worksheet where you want the results, enter the first criterion in cell A2 and the second criterion in cell B2. Adjust these cell references if your criteria are stored elsewhere.
Click the cell where the matching name list should begin, such as D2. Make sure the cells below D2 are blank.
Type =FILTER(Data!B2:B1000,(Data!E2:E1000=A2)*(Data!S2:S1000=B2),"No matches") and press Enter. Replace Data with your source worksheet name and update the ranges as needed.
Edit the values in A2 or B2. WPS Spreadsheets recalculates the formula and spills the names that match both criteria automatically.
Use a helper column when FILTER is unavailable
Use this alternative for spreadsheet versions that do not support dynamic-array formulas such as FILTER.
Build a Dynamic Two-Criteria Name List with WPS Spreadsheets
WPS Spreadsheets can use FILTER to extract names from a source column when two related columns match criteria on another worksheet. The spilled result updates as you change the criteria, making it useful for searchable lists, reports, and record lookups.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the workbook containing the Data worksheet and criteria worksheet.
- 2. Enter the criteria: On the results worksheet, type the two values to match in separate cells, such as A2 and B2.
- 3. Insert the FILTER formula: Click the first result cell and enter =FILTER(Data!B2:B1000,(Data!E2:E1000=A2)*(Data!S2:S1000=B2),"No matches").
- 4. Review the spilled results: Press Enter and verify that the names expand below the formula cell. Change either criterion cell to return a different list.
Frequently Asked Questions
How do I match two criteria with FILTER in WPS Spreadsheets?
Use separate Boolean tests inside FILTER and multiply them for an AND condition: =FILTER(return_range,(criteria_range1=criterion1)*(criteria_range2=criterion2),"No matches").
Why does my FILTER formula return no names when matching records exist?
Check that the criteria values do not contain extra spaces, that text and numbers use matching formats, and that the return range and both criteria ranges have identical start and end rows.
Can the criteria be stored on a different worksheet?
Yes. Reference the worksheet and cell directly, such as Criteria!A2 and Criteria!B2. For example, use (Data!E2:E1000=Criteria!A2)*(Data!S2:S1000=Criteria!B2).
What does a spill error mean in an automatic name list?
A spill error means WPS Spreadsheets cannot place all returned names in the cells below the formula. Clear or move any values, merged cells, or other content blocking the output range.
Can I return more than just the name column?
Yes. Change the first FILTER argument from a single column, such as Data!B2:B1000, to a multi-column range, such as Data!A2:S1000, to return complete matching records.
