Fix #N/A in Excel VLOOKUP for Inconsistent Serial Numbers
Question details
Fix a VLOOKUP that returns #N/A even though the required serial number appears to exist in the lookup table.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is matching serial numbers from one worksheet against a serial-number lookup table.
- Observed behavior
- VLOOKUP returns #N/A for some visible matches because the serial values may use inconsistent data types or contain hidden characters.
Inspect a failing serial number and its matching lookup key with ISTEXT and ISNUMBER to identify mixed data types. If the values were imported, check for leading, trailing, or nonbreaking spaces before changing the lookup formula.
Normalize serial numbers to text in the lookup formula
Use this method when serial numbers may be stored as text in one range and as numbers in the other range.
Appending an empty text string converts both the lookup value and the lookup array to text for comparison. This prevents mismatches caused only by different number and text storage types.
In the result cell, enter =XLOOKUP($D2&"",Serial!$A$2:$A$1000&"",Serial!B$2:B$1000,"") to compare both serial-number ranges as text.
Select the formula cell and drag the fill handle across columns or down rows as needed. Keep $D2 and the Serial sheet ranges aligned with your worksheet.
If XLOOKUP is unavailable, enter =IFERROR(INDEX(Serial!B$2:B$1000,MATCH($D2&"",Serial!$A$2:$A$1000&"",0)),"") to return a blank when no normalized exact match is found.
Remove hidden spaces and nonprinting characters from serial numbers
Use this method when the displayed serial numbers look identical but continue to return #N/A after data-type normalization.
Clean serial numbers and match them reliably in WPS Spreadsheets
WPS Spreadsheets provides familiar formulas and worksheet tools to standardize imported serial numbers, remove hidden spaces, and use exact-match lookups without changing the layout of your workbook.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the affected .xlsx or .xls file.
- 2. Add a cleaned serial-number column: Click an empty column beside the serial numbers and enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) for the first source value.
- 3. Fill the cleaning formula: Select the formula cell and drag the fill handle down to normalize every serial number in the column.
- 4. Apply an exact-match lookup: In the output cell, use =IFERROR(INDEX(Serial!B$2:B$1000,MATCH($D2&"",Serial!$A$2:$A$1000&"",0)),"") and adjust the ranges for your sheet.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the serial number is visible in the table?
The values may look the same but be stored differently, such as a number in one cell and text in another. Hidden spaces, nonbreaking spaces, and imported characters can also prevent an exact match.
How can I tell whether a serial number is text or a number?
Enter =ISTEXT(A2) to test for text and =ISNUMBER(A2) to test for a numeric value. Replace A2 with the serial-number cell you want to inspect.
What formula removes nonbreaking spaces from imported serial numbers?
Use =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). It replaces the nonbreaking space character, removes nonprinting characters, and trims extra spaces.
Should serial numbers be converted to numbers before using VLOOKUP?
Not if leading zeros are part of the serial number. Standardize both lookup ranges as text instead, for example by using &"" in the lookup comparison or by creating cleaned text helper columns.
How do I return a blank instead of #N/A for a missing serial number?
Wrap the lookup in IFERROR, such as =IFERROR(VLOOKUP(D2,Serial!A:B,2,FALSE),""). This shows a blank only when the lookup produces an error.
