Filter Excel Values by a Criteria Column in WPS Spreadsheet
Question details
Return numbers from Sheet2 column A only when the corresponding value in column M is greater than or equal to 1.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants a filtered list on Sheet1 based on a criteria column located on Sheet2.
- Observed behavior
- Rows whose Sheet2 column M value is 0 are excluded, while rows with values of 1 or higher are returned from Sheet2 column A.
Confirm that the return range and criteria range start and end on the same rows. Make sure the cells below the formula are empty so the filtered results can spill into them.
Use the FILTER function to return matching values
Use this method in WPS Spreadsheet when FILTER is available and you want the results to update automatically as Sheet2 data changes.
FILTER evaluates the values in Sheet2 column M and returns the corresponding entries from column A only where the criterion is met.
Open Sheet1 and select the cell where you want the filtered list to begin, such as A2.
Type =FILTER(Sheet2!A2:A11,Sheet2!M2:M11>=1,"No matches") and press Enter.
WPS Spreadsheet returns the matching values from Sheet2 column A in consecutive cells. Values linked to a 0 in column M are not included.
Use an INDEX and SMALL array formula when FILTER is unavailable
Use this alternative when your spreadsheet version does not support dynamic-array FILTER formulas.
Create a criteria-based filtered list with WPS Spreadsheet
WPS Spreadsheet lets you use the FILTER function to extract values from one column when a corresponding criteria column meets a condition. Enter the formula once, and the output list updates when the source data changes.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, open the workbook, and select the Sheet1 worksheet.
- 2. Choose an output cell: Click the cell where the first filtered value should appear, such as A2.
- 3. Apply the criteria formula: Enter =FILTER(Sheet2!A2:A11,Sheet2!M2:M11>=1,"No matches") and press Enter.
- 4. Expand the ranges for your data: Edit the A and M ranges to cover all source rows while keeping both ranges the same size.
Frequently Asked Questions
How do I exclude zeros when filtering values from another Excel sheet?
Use a condition of >=1 in the include argument, such as =FILTER(Sheet2!A2:A11,Sheet2!M2:M11>=1,"No matches"). This returns only values whose matching column M value is 1 or greater.
Why does my FILTER formula show a spill error?
A spill error usually means cells in the intended output area contain data. Clear the cells below and beside the formula cell, then press Enter again.
Can the return column and criteria column be on different sheets?
Yes. Reference each range with its sheet name, for example Sheet2!A2:A11 for the values to return and Sheet2!M2:M11 for the criteria.
What should I do if FILTER is not available in my spreadsheet version?
Use an INDEX, SMALL, and IF array formula, then fill it down. In versions that require legacy array formulas, confirm the formula with Ctrl+Shift+Enter.
