Function Problems

WPS Spreadsheet: Filter Table Rows Between Two Numeric Limits

Bushra ParveenBushra Parveen Nov 12, 2026 868 views

Question details

The user needs to return table rows whose stored numeric range contains a user-entered number.

WPS Spreadsheet: Filter Table Rows Between Two Numeric Limits
Product
WPS Spreadsheet
Device & OS
not provided
Scenario
A table stores ranges such as 20-40, and a user-entered value must be compared with each range to return matching rows.
Observed behavior
The range limits are combined in text, so the input value must be compared with numbers extracted from the text or with separate minimum and maximum columns.
Before you start

Confirm that each range uses the same hyphen format, such as 20-40, and identify the cell or named range containing the input number. For the most reliable formulas, create separate Minimum and Maximum columns in the table.

Solution 1Recommended

Use separate Minimum and Maximum columns for reliable filtering

Use this recommended structure when you can modify the table; it avoids text parsing and makes the criteria easier to maintain.

Store the lower and upper limits in separate numeric columns named Minimum and Maximum. The FILTER formula can then compare the input directly with both columns.

1
Create numeric limit columns

In the table, add columns named Minimum and Maximum, and enter values such as 20 and 40 as numbers rather than storing them together as 20-40.

2
Enter the filtering formula

In an empty cell, enter =FILTER(Table1,(input>=Table1[Minimum])*(input<=Table1[Maximum]),"None found"), replacing input with the input cell reference or named range.

3
Check the returned rows

Press Enter and review the spilled results. WPS Spreadsheet returns every Table1 row where the input is greater than or equal to Minimum and less than or equal to Maximum.

Inclusive limits: The >= and <= operators include rows when the input exactly matches the minimum or maximum value.

Filter Numeric Range Matches Directly in WPS Spreadsheet

WPS Spreadsheet lets you organize minimum and maximum values in a table and use FILTER formulas to return matching rows. It also supports formulas that split text ranges when the limits are stored together.

  1. 1. Open your spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the range table.
  2. 2. Prepare the table: Select the range and use the table tools to confirm the source table name, or add separate Minimum and Maximum columns beside the existing Range column.
  3. 3. Enter the filter formula: Click a blank output cell and enter the appropriate FILTER formula, using your input cell and table column names.
  4. 4. Verify the results: Press Enter and confirm that the spilled rows include only records whose numeric limits contain the input value.
Return all matching table rows from one input value with FILTER.Use separate numeric columns to simplify range comparisons and maintenance.Work with familiar spreadsheet formulas and table references.Open and save Microsoft Excel XLSX files with reliable format compatibility.
microsoft office alternative - wps office

Frequently Asked Questions

How do I filter a WPS Spreadsheet table between two numbers?

Use =FILTER(Table1,(input>=Table1[Minimum])*(input<=Table1[Maximum]),"None found") after placing the lower and upper limits in separate numeric columns. Replace input with the input cell or named range.

Can I filter ranges stored as text like 20-40?

Yes. Use =LET(low,--TEXTBEFORE(Table1[Range],"-"),high,--TEXTAFTER(Table1[Range],"-"),FILTER(Table1,(input>=low)*(input<=high),"None found")). The text must contain a hyphen between two numeric values.

Are the minimum and maximum values included in the results?

Yes. The >= and <= operators include rows when the input equals either boundary. Change them to > or < if the boundary should be excluded.

Why does the formula return no results?

Check that the input is numeric, the range text uses a consistent hyphen, and the table and column names match the formula. The formula displays None found when no row meets both conditions.

What is the best table design for numeric range filtering?

Use separate Minimum and Maximum columns with numeric values. This avoids splitting text, makes sorting and validation easier, and supports simpler FILTER criteria.