Create a Unique Filtered List with Multiple Criteria in WPS Spreadsheets
Question details
Create a distinct list of values from another worksheet by matching a source criteria column against multiple criteria cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to consolidate values from the Load Tracker worksheet when values in column D match any criterion listed in K1:K4, then remove duplicates from the returned values.
- Observed behavior
- The desired result is a dynamic unique list containing only matching values from Load Tracker column M.
Confirm that the criteria are entered in K1:K4 and that the source ranges on the Load Tracker sheet begin and end on matching rows. Make sure the output cells below the formula are empty so the dynamic-array results can spill.
Use FILTER, XMATCH, and UNIQUE to return matching distinct values
Use this formula when you want a dynamic list of unique values from Load Tracker column M for any matching criterion in K1:K4.
XMATCH checks whether each value in Load Tracker column D appears in the criteria list. FILTER returns the corresponding values from column M, and UNIQUE removes repeated results.
Click the cell where the unique filtered list should begin, such as L1 or another blank cell outside the source data.
Type or paste =LET(filtered,FILTER('Load Tracker'!$M$2:$M$5000,ISNUMBER(XMATCH('Load Tracker'!$D$2:$D$5000,$K$1:$K$4))),UNIQUE(filtered)) and press Enter.
WPS Spreadsheets returns the matching values from Load Tracker column M as a unique list. Edit the values in K1:K4 to refresh the result automatically.
Display a message when no criteria produce results
Use this version if the formula may return no matching rows and you want a clear result instead of an empty-array error.
Build Dynamic Multi-Criteria Unique Lists in WPS Spreadsheets
WPS Spreadsheets lets you combine dynamic-array functions such as FILTER, XMATCH, UNIQUE, and LET to create a clean list of distinct matching values across worksheets. Enter the formula once and update the criteria cells whenever you need a new result.
- 1. Open the workbook in WPS Spreadsheets: Open your .xlsx file in WPS Office and select the worksheet where the result list should appear.
- 2. Enter the criteria: Place the values to match in cells K1:K4, using one criterion per cell.
- 3. Choose an output cell: Click an empty cell with enough blank cells below it for the spilled unique list.
- 4. Enter the dynamic-array formula: Enter =LET(filtered,FILTER('Load Tracker'!$M$2:$M$5000,ISNUMBER(XMATCH('Load Tracker'!$D$2:$D$5000,$K$1:$K$4))),UNIQUE(filtered)) and press Enter.
Frequently Asked Questions
What does XMATCH do in this multi-criteria formula?
XMATCH searches each value in 'Load Tracker'!D2:D5000 within K1:K4. ISNUMBER converts successful matches into TRUE values that FILTER can use as its include argument.
Why does the formula return duplicate values?
Use UNIQUE around the FILTER result: UNIQUE(filtered). If duplicates remain, check for differences such as leading or trailing spaces in the values from Load Tracker column M.
How can I include more than four criteria?
Expand the criteria reference in the XMATCH argument. For example, replace $K$1:$K$4 with $K$1:$K$20 after entering additional criteria in that range.
Why do I see a spill error after entering the formula?
The formula returns multiple results and needs blank cells below its starting cell. Clear or move any content blocking the spill range, then recalculate the formula.
