Use XLOOKUP for Values Within Numeric Ranges in WPS Office Spreadsheets
Question details
The user needs to match a numeric value to a range band such as 0-100, 101-150, or 151-200 and return the corresponding percentage or result using XLOOKUP.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with range labels and a value to classify, and wants to return the correct result for the matching numeric range.
- Observed behavior
- The formula should classify the value into the correct band and return the associated percentage or result.
Confirm that your version of WPS Office Spreadsheets includes XLOOKUP and that the range labels are stored consistently with a hyphen separator and sorted ascending, such as 0-100, 101-150, 151-200, so the upper limits can be extracted and matched reliably.
Use XLOOKUP Approximate Match with Extracted Upper Limits
Best when your range bands are stored in a single text column such as 0-100 and you need to classify a value by its upper limit.
This method uses MID and FIND to extract the number after the hyphen in each range label, VALUE to convert it to a number, and XLOOKUP with match mode 1 to return the exact or next larger upper limit. It assumes the range labels are in A1:A4, the results are in B1:B4, and the lookup value is in F2.
Launch WPS Office, open the worksheet that contains the range labels and results, and click the cell where the result should appear.
In the formula bar, type =XLOOKUP(F2,VALUE(MID($A$1:$A$4,FIND("-",$A$1:$A$4)+1,LEN($A$1:$A$4))),$B$1:$B$4,,1) and press Enter.
Enter values such as 100, 101, 150, and 151 in F2 to confirm that the formula returns the correct percentage or result for each range band.
Create Separate Lower-Limit Columns for a Simpler XLOOKUP
Use this when you can change the worksheet layout and prefer a simpler formula without text parsing.
Classify Numeric Values into Ranges with XLOOKUP in WPS Office Spreadsheets
WPS Office Spreadsheets can complete this range lookup directly using Excel-compatible XLOOKUP formulas. Parse the upper limit from range labels, use approximate match mode, and return the matching result without changing your normal worksheet layout.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook from the home screen or by clicking Menu > Open.
- 2. Enter the XLOOKUP range formula: Select the result cell and type =XLOOKUP(F2,VALUE(MID($A$1:$A$4,FIND("-",$A$1:$A$4)+1,LEN($A$1:$A$4))),$B$1:$B$4,,1), then press Enter.
- 3. Test boundary values: Enter 100, 101, 150, and 151 in the lookup cell to confirm the correct result is returned for each band.
- 4. Save in Excel-compatible format: Click Menu > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel and WPS Office.
Frequently Asked Questions
Does WPS Office support the XLOOKUP function?
Current versions of WPS Office Spreadsheets support XLOOKUP and Excel-compatible formulas. If the function is not available, update WPS Office from the official download page.
Why does my XLOOKUP range formula return #N/A?
This usually means the lookup array does not contain a matching value. For range bands, include match mode 1 as the fifth argument so XLOOKUP returns the exact or next larger upper limit, and check that range labels use the expected hyphen separator.
Can I use this formula when ranges are written like 0-100?
Yes. The formula extracts the upper limit after the hyphen with FIND and MID, converts it to a number with VALUE, and uses those upper limits as the XLOOKUP lookup array.
How do I adjust the formula if my range labels and results are in different columns?
Replace $A$1:$A$4 with the range that contains your range labels and $B$1:$B$4 with the corresponding result range, then keep F2 as the cell containing the value to classify.
