Formula Errors

Fix Excel VLOOKUP #N/A Errors Caused by Numeric and Text Values

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

Find matches when VLOOKUP returns #N/A for numeric-looking values but works for alphanumeric values.

Fix Excel VLOOKUP #N/A Errors with Numeric and Text 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.
Before you start

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.

Solution 1Recommended

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.

1
Create a normalized lookup column

If the source values are in B2:B100, enter =IFERROR(VALUE(B2),B2) in D2 and fill the formula down through D100.

2
Place return values beside the helper

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.

3
Enter the exact-match formula

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.

4
Check remaining errors

If the result is still #N/A, compare A2 with the normalized values in column D and check for missing entries or extra spaces.

Use an Exact Match and Normalize Numeric-Looking Values
Work with VLOOKUP in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 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. 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.
Normalize mixed numeric and text lookup values with a helper columnUse VLOOKUP with FALSE for exact matchesOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.