WPS Spreadsheet: Return Text from Numbers with XLOOKUP
Question details
The user needs to return text based on a number when the correct text is stored in a mapping table and may vary by row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A lookup formula must retrieve the corresponding text from a number-and-text mapping table, including situations with duplicate numbers.
- Observed behavior
- A simple IF formula cannot reliably return different text for the same number because the result depends on the matching row or an additional condition.
Confirm which column contains the lookup numbers and which column contains the text to return. If numbers repeat, identify the second column or criterion that distinguishes the correct row before entering the formula.
Use XLOOKUP with a number-to-text mapping table
Use this solution when each lookup number has one relevant text result in the mapping table.
XLOOKUP searches the number column and returns the corresponding value from the text column. Keep the mapping ranges absolute so the formula continues to use the same table when copied.
For the example below, the lookup number is in BU2, the number mapping is BU2:BU7, and the return text is BV2:BV7. Enter the formula in the result cell, not in the lookup cell or inside the source mapping range.
In WPS Spreadsheet, click the cell where the text result should appear. Do not place the formula in BU2 or in a cell that is part of the source mapping range.
Type =XLOOKUP(BU2,$BU$2:$BU$7,$BV$2:$BV$7,"") and press Enter. The formula searches BU2:BU7 and returns the matching text from BV2:BV7.
Drag the fill handle or use Copy and Paste to apply the formula to other result cells. The dollar signs keep the mapping ranges fixed while the lookup reference changes by row.
Add a second criterion for duplicate numbers
Use this method when the same number appears more than once and another column determines which text should be returned.
Build Reliable Number-to-Text Lookups in WPS Spreadsheet
WPS Spreadsheet lets you create XLOOKUP formulas that return row-specific text from a mapping table, keep lookup ranges fixed, and add criteria when numbers are duplicated. Its familiar spreadsheet interface supports a smooth transition from Excel workbooks.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the number and text mapping table.
- 2. Create or review the mapping table: Place the lookup numbers in one column and the corresponding return text in another column, then confirm the ranges used by the formula.
- 3. Enter the lookup formula: Click the result cell and enter =XLOOKUP(BU2,$BU$2:$BU$7,$BV$2:$BV$7,"") using your actual lookup cell and mapping ranges.
- 4. Add criteria for duplicates: If a number appears in multiple rows, use a second comparison such as (BW2:BW7=BW2) in a multi-condition XLOOKUP formula.
Frequently Asked Questions
Why does a simple IF formula fail when the same number has different text results?
IF uses conditions you define directly and does not automatically identify the correct row in a mapping table. XLOOKUP can retrieve the text associated with the matching row, while an additional criterion resolves duplicate numbers.
Where should the XLOOKUP formula be entered?
Enter the formula in a separate result cell. Do not place it in the lookup cell BU2 or inside the source ranges if doing so would overwrite the lookup data or create a circular reference.
What does the last empty pair of quotation marks do in XLOOKUP?
The final "" argument tells XLOOKUP to return a blank when no match is found. Without it, an unmatched number normally produces a not-found error.
How does XLOOKUP behave when the lookup number appears more than once?
XLOOKUP returns the first matching row by default. Use a second condition, such as a category or row-specific identifier, when the correct result depends on more than the number.
Can WPS Spreadsheet open an Excel workbook containing this formula?
WPS Spreadsheet can open and edit common Microsoft Excel XLSX and XLS files. Formula behavior may depend on the WPS version and the functions supported in the workbook, so verify the result after opening the file.
