How to Use Cell Values, Named Ranges, and XLOOKUP Correctly in WPS Spreadsheets
Question details
Clarify that a text value such as Barbara in a cell is not automatically interpreted as a named range, and show how to use it as a lookup criterion with XLOOKUP and named ranges.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building lookup formulas to retrieve matching data from named ranges or a table based on a value entered in a worksheet cell.
- Observed behavior
- A cell value is not automatically treated as a named range name; formulas may return #N/A or refer to the wrong item when names are shared between headings and ranges.
Identify the lookup range and return range in your workbook, and make sure the cell used as the lookup value matches an item in the lookup range exactly, including spaces and capitalization.
Use XLOOKUP with a Cell Value and Named Ranges
Use this method when the value to find is entered in a worksheet cell and the source data is stored in named ranges.
In WPS Spreadsheets, a text value such as Barbara in A1 is not automatically treated as a range name. Instead, use the cell reference as the lookup value so XLOOKUP searches for the text itself.
Click the cell where you want the returned value to appear.
Type =XLOOKUP(A1,Names,Values) and press Enter. Replace A1 with the cell that contains Barbara, and replace Names and Values with your actual named ranges.
To avoid an #N/A error when there is no match, use =XLOOKUP(A1,Names,Values,"Not found").
Use Structured Table References to Avoid Name Conflicts
Use this method when column headings and named ranges share the same word, or when you want formulas that clearly identify their source data.
Use XLOOKUP and Named Ranges Correctly in WPS Spreadsheets
In WPS Spreadsheets, use the formula =XLOOKUP(A1,Names,Values) to search for the value in a cell and return the matching result from a named range. For safer and clearer formulas, use named ranges directly or format data as a table and use structured references.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open WPS Spreadsheets, and load the workbook that contains your named ranges.
- 2. Select the result cell: Click the cell where you want the XLOOKUP result to appear.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP(A1,Names,Values) and replace A1 with your lookup cell, Names with the lookup range, and Values with the return range.
- 4. Add an error message if needed: Use =XLOOKUP(A1,Names,Values,"Not found") to display a custom message instead of #N/A.
- 5. Press Enter and verify: Press Enter and test with different values in the lookup cell to confirm the correct result is returned.
Frequently Asked Questions
Why does a cell value like Barbara not work as a named range?
In WPS Spreadsheets, cell text is treated as data rather than as a reference to a named range. Use the value as a lookup criterion, for example =XLOOKUP(A1,Names,Values), to search for the text in A1.
Why does my XLOOKUP formula return #N/A even though the value appears to be present?
The lookup value is not an exact match in the lookup range. Check for extra spaces, hidden characters, or differing capitalization, or add a fourth argument such as "Not found" to handle missing matches.
Can I use INDIRECT with a cell value to build a range reference?
Yes, but INDIRECT is fragile because the text must exactly match the range name and it does not update like structured references. A safer option is to use XLOOKUP with a cell value or format the data as a table.
How do I create a named range in WPS Spreadsheets?
Select the cells you want to name, click the Name Box to the left of the formula bar, type the name, and press Enter. You can then use that name directly in formulas such as =XLOOKUP(A1,Names,Values).
Can WPS Spreadsheets open an Excel workbook that contains XLOOKUP?
Yes. WPS Spreadsheets supports .xlsx workbooks and Excel functions such as XLOOKUP and named ranges, so existing formulas can be opened and edited.
