How to Use an Excel XLOOKUP Formula in WPS Office Spreadsheets to Return Text by Score Range
Question details
The user wants an Excel-compatible formula in WPS Office to convert a numeric score into a text label such as Difficult, Moderate, or Easy by comparing the score against defined threshold ranges.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Analyzing score data and assigning descriptive labels based on score thresholds.
- Observed behavior
- The user needs a formula that returns a text label such as Difficult, Moderate, or Easy when a score falls within a defined range.
Create or identify a score threshold table with a Minimum column and a Description column, and make sure the score values are numeric. If XLOOKUP is not available, update WPS Office to a version that includes XLOOKUP support.
Use XLOOKUP with a Threshold Table for Flexible Score Labels
Best for maintainable and scalable scoring rules because the thresholds and labels are stored in a separate table.
XLOOKUP approximate matching finds an exact score or the next smaller threshold, so every score falls into the correct band without writing a long nested formula.
In WPS Office Spreadsheets, select a blank worksheet area and enter the minimum score in one column and the corresponding label such as Difficult, Moderate, or Easy in the next column.
Click the cell where you want the text result to appear, for example cell B2 next to the first score.
Type =XLOOKUP(A2,Thresholds[Minimum],Thresholds[Description],,-1) and press Enter. Replace the table name and column headers with your actual threshold table references.
Drag the fill handle from the formula cell down through the remaining score rows to apply the same text rule to all scores.
Use IFS for Small Fixed Score Ranges Without a Table
Useful when the score ranges are unlikely to change and you prefer a single formula without helper cells.
Use WPS Office Spreadsheets to Convert Score Ranges to Text Labels
WPS Office Spreadsheets supports Excel-compatible XLOOKUP and IFS formulas, so you can create a score threshold table and return descriptive text labels without changing your existing workflow.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and click Spreadsheets to open a blank workbook or your existing score data file.
- 2. Create the threshold table: In a blank area, enter Minimum in one column and Description in the next, then list the lower bound for each score band and its label.
- 3. Enter the XLOOKUP formula: In the result cell, type =XLOOKUP(A2,Thresholds[Minimum],Thresholds[Description],,-1) or adjust the references to match your table.
- 4. Save the workbook: Click Menu and choose Save As, then select .xlsx format to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Does WPS Office support XLOOKUP for approximate matching?
Yes. WPS Office Spreadsheets supports XLOOKUP with the -1 match mode for exact match or next smaller item. If the function is not available, update WPS Office to the latest version.
Can I return text labels without creating a threshold table?
Yes. For a small number of fixed ranges, use =IFS(A2>=80,"Difficult",A2>=50,"Moderate",TRUE,"Easy"). A threshold table is easier to manage when ranges or labels change frequently.
What does the -1 argument in XLOOKUP do?
The -1 argument tells XLOOKUP to match an exact value or, if no exact match exists, use the next smaller value. This makes it return the correct threshold band for scores that fall between listed minimums.
Can I use VLOOKUP instead of XLOOKUP?
Yes, if the threshold table is sorted ascending, use =VLOOKUP(A2,ThresholdTable,2,TRUE). XLOOKUP is more flexible because the lookup column does not need to be the first column.
How do I make the formula update when I change score thresholds?
Store thresholds in a structured Excel table and reference the table columns in XLOOKUP. When you edit or add rows to the threshold table, the formula results update automatically.
