Fix VLOOKUP Returning #N/A for Text-Based Door Codes in WPS Spreadsheets
Question details
The user needs to retrieve door-code descriptions from a table where four-digit codes are stored as text and the code column is not the leftmost column, which causes VLOOKUP to return #N/A.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up text-based four-digit door codes in a source table where the code column is the second column.
- Observed behavior
- VLOOKUP returns #N/A because it only searches the first column of the lookup range; the text codes are in the second column and are stored as text.
Confirm that the door-code cells are formatted as text and do not contain extra spaces, hidden characters, or inconsistent number-as-text entries. Identify the exact column positions of the CODE and DESCRIPTION columns before changing the formula.
Use XLOOKUP to Look Up Text Codes from Any Column
Use this method in WPS Spreadsheets versions that support XLOOKUP to search the text code column separately and return the matching description.
VLOOKUP requires the lookup column to be the first column in the specified table array. XLOOKUP separates the lookup array from the return array, so you can search for text door codes in the CODE column even when it is the second column.
Click the first cell where you want the door-code description to appear.
Type =XLOOKUP([@CODE],Table13[CODE],Table13[DESCRIPTION],"") and adjust the table and column names to match your workbook.
Press Enter to confirm the formula, then use the fill handle to copy it to the remaining rows.
Use INDEX and MATCH as an Exact-Match Alternative
Use this method in WPS Spreadsheets if XLOOKUP is not available or when you need compatibility with older workbook versions.
Fix Text Door Code Lookups in WPS Spreadsheets
Use XLOOKUP or INDEX/MATCH in WPS Spreadsheets to search text-based door codes in any column, preserve leading zeros, and return the correct description without rearranging the source table.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open your Excel workbook, and go to the worksheet where the door-code lookup result should appear.
- 2. Select the result cell: Click the first cell in the description column where you want the door-code description to be returned.
- 3. Enter the recommended formula: Type =XLOOKUP([@CODE],Table13[CODE],Table13[DESCRIPTION],"") or, for older versions, =INDEX(Table13[DESCRIPTION],MATCH([@CODE],Table13[CODE],0)).
- 4. Apply the formula to the data rows: Press Enter and use the fill handle to copy the formula down the table.
- 5. Verify the returned descriptions: Check that text codes with leading zeros now return the correct descriptions and that no #N/A remains.
Frequently Asked Questions
Why does VLOOKUP return #N/A if the door codes are stored as text on both worksheets?
The main issue in this scenario is column order: VLOOKUP only searches the first column of the table array. If the four-digit code column is the second column in the source table, VLOOKUP looks in the wrong column and returns #N/A even when both ranges are formatted as text.
Does XLOOKUP work in WPS Spreadsheets?
Current WPS Office versions that include the XLOOKUP function can use it in WPS Spreadsheets. If your version does not list XLOOKUP, use INDEX/MATCH instead, which works without the first-column lookup restriction.
How can I keep leading zeros in four-digit door codes?
Format the code cells as Text before entering the values, or import them as text. XLOOKUP and INDEX/MATCH return the matching text without converting the lookup value to a number, so leading zeros are preserved.
Can INDEX/MATCH search a column that is not the first column?
Yes. MATCH searches the exact CODE column you specify, and INDEX returns the value from the DESCRIPTION column you specify, so the lookup column does not need to be the leftmost column.
What does the empty "" at the end of the XLOOKUP formula do?
The fourth argument is the if_not_found value. In =XLOOKUP([@CODE],Table13[CODE],Table13[DESCRIPTION],""), the "" returns an empty cell instead of #N/A when a code is not found.
