Use XLOOKUP to Assign Labels Based on Numeric Ranges in WPS Spreadsheets
Question details
The user needs to assign a label such as 0005, 0020, or 0500 to a numeric value based on the largest upper limit that is greater than or equal to the lookup value using XLOOKUP approximate matching.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up a numeric value in a threshold table and returning the corresponding range label from an adjacent column.
- Observed behavior
- The original XLOOKUP formula returned an error or did not return the expected range label until match mode 1 was applied.
Open the workbook that contains the numeric upper limits and labels, and confirm that the upper-limit column contains numbers, not text, sorted in ascending order.
Use XLOOKUP Match Mode 1 to Return the Next Larger Threshold Label
Use this solution when the lookup table is sorted in ascending order and you need XLOOKUP to return the label whose upper limit is the next larger value or an exact match.
XLOOKUP's fifth argument is match_mode. Setting it to 1 tells WPS Spreadsheets to find an exact match or the next larger item, which is ideal for threshold or range-label tables.
Open the workbook in WPS Spreadsheets and click the cell where you want the range label to appear.
Type =XLOOKUP(A45,$A$2:$A$15,$B$2:$B$15,,1) and press Enter. Here A45 is the lookup value, A2:A15 contains the numeric upper limits, and B2:B15 contains the labels.
Check that the formula returns the correct label for the next larger limit. For a lookup value of 3445, it returns the label associated with 3500.
Assign Range Labels with XLOOKUP in WPS Spreadsheets
Use WPS Spreadsheets to apply XLOOKUP approximate matching so any numeric value automatically returns the correct threshold label from your lookup table.
- 1. Open the workbook: Launch WPS Office and open the spreadsheet that contains the numeric lookup table and the value to evaluate.
- 2. Select the output cell: Click the cell where the range label should be displayed.
- 3. Enter the XLOOKUP function: Type =XLOOKUP(A45,$A$2:$A$15,$B$2:$B$15,,1) in the formula bar.
- 4. Press Enter and review: Press Enter to calculate the result and confirm that the label matches the expected threshold.
Frequently Asked Questions
Why does XLOOKUP return #N/A for a value below the smallest limit?
With match_mode 1, XLOOKUP looks for an exact match or the next larger item. If the lookup value is less than the first numeric limit, there is no next larger item, so WPS Spreadsheets returns #N/A.
What does the match_mode argument 1 do in XLOOKUP?
Match mode 1 enables approximate matching by returning an exact match or, when no exact match exists, the next larger item in the lookup array.
Can the return labels be text values such as 0005?
Yes. The lookup column must contain numeric limits, but the return column can contain text, numbers, or formatted labels such as 0005, 0020, or 0500.
Do I need to sort the lookup column for XLOOKUP approximate match?
Yes. For match mode 1 to work correctly in WPS Spreadsheets, the numeric lookup limits should be sorted in ascending order before entering the formula.
