Fix Excel VLOOKUP #N/A Errors Caused by Numeric and Text Values
Question details
Find matches when VLOOKUP returns #N/A for numeric-looking values but works for alphanumeric values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A lookup value and the first column of a lookup table may store matching-looking values as different data types.
- Observed behavior
- VLOOKUP returns #N/A for some numeric-looking values while returning results for alphanumeric values.
Check the lookup formula, the lookup value, and the first column of the lookup range. If the values are identifiers with meaningful leading zeros, treat them consistently as text rather than converting them to numbers.
Use an Exact Match and Normalize Numeric-Looking Values
Use this method when numbers and numeric-looking text should represent the same values, while alphanumeric entries should remain text.
Changing a cell's format to Number does not convert text already stored in the cell. A helper column can convert numeric-looking text while leaving alphanumeric values unchanged.
This method removes leading zeros from numeric-looking values and may convert other text that Excel recognizes as a number. Do not use it when those distinctions are meaningful.
If the source values are in B2:B100, enter =IFERROR(VALUE(B2),B2) in D2 and fill the formula down through D100.
Put the corresponding values to return in E2:E100, preserving the same row order as B2:B100. VLOOKUP searches the first column of its selected range.
For a lookup value in A2, enter =VLOOKUP(IFERROR(VALUE(A2),A2),$D$2:$E$100,2,FALSE). FALSE requires an exact match; 0 can be used instead.
If the result is still #N/A, compare A2 with the normalized values in column D and check for missing entries or extra spaces.

Keep Leading-Zero Identifiers as Text
Use text values on both sides when leading zeros distinguish identifiers.
Fix VLOOKUP Type Mismatches in WPS Spreadsheets
Use WPS Spreadsheets to normalize numeric-looking values and perform an exact-match VLOOKUP while retaining alphanumeric entries.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Normalize the source values: With source values in B2:B100, enter =IFERROR(VALUE(B2),B2) in D2 and fill down through D100. Use this conversion only if leading zeros are not significant.
- 3. Arrange the lookup range: Place the corresponding return values in E2:E100 so the normalized values are in the first column of D2:E100.
- 4. Enter the lookup formula: For a lookup value in A2, enter =VLOOKUP(IFERROR(VALUE(A2),A2),$D$2:$E$100,2,FALSE) in the result cell.

Frequently Asked Questions
Why does VLOOKUP return #N/A when the values look identical?
One value may be stored as a number and the other as text. Check both the lookup value and the first column of the lookup range, then convert them consistently.
Does changing a cell's format to Number convert text to a number?
No. Changing the number format alone does not convert an existing text value. Re-enter the value or use a formula such as =VALUE(A2) when it is appropriate to convert it.
What does FALSE do in a VLOOKUP formula?
FALSE, or 0, as the fourth argument requests an exact match. For example, =VLOOKUP(A2,B2:C100,2,FALSE) searches for an exact match in column B.
How can I avoid losing leading zeros in lookup IDs?
Store identifiers consistently as text in both the lookup cell and the source column. Select Text from the number format menu before re-entering the IDs; do not use VALUE when the zeros are significant.




