#N/A when searching Numbers stored as Text

An #N/A error occurs during an Excel lookup when a formula searches for a true numeric value inside a column where the values are stored as text strings. Even if the numbers look identical on your screen—such as 10542 matching 10542—Excel strictly treats numeric values and text strings as incompatible data types. Because an exact lookup will never match a numeric 10542 to a text string "10542", the lookup engine concludes that the value does not exist and returns #N/A.

Quick Answer

To resolve the error immediately in your formula, coerce the numeric lookup value into text by appending an empty string: =XLOOKUP(A2&"", B:B, C:C) or =VLOOKUP(A2&"", B:E, 4, FALSE). To permanently fix the source table instead, select the text-formatted numbers, open the Data tab, click Text to Columns, and immediately click Finish to convert them into true numbers.

---

Why the Data Type Mismatch Happens

Excel distinguishes between numbers and text strings at the binary level. When an accounting platform, CRM, or database exports data to a CSV or .xlsx file, account codes, phone numbers, and product IDs are often wrapped in quotes or formatted as text.

When you type a search value manually into a cell, Excel automatically evaluates it as a standard number. When VLOOKUP, XLOOKUP, or MATCH compares your raw number against the text-formatted export column, the evaluation fails:

10542 = "10542" --> FALSE

Because the comparison evaluates to FALSE, the lookup scans the entire column, fails to find an identical data type, and throws #N/A.

---

Step-by-Step Diagnostic Check

Step 1: Diagnose the Mismatch

Before altering your formulas or source tables, confirm that data types are the underlying issue.

1. Check Cell Alignment: By default, unformatted Excel numbers align to the right side of the cell. Text strings align to the left. If your lookup value sits on the right and your lookup table values sit on the left, you are dealing with a text-number mismatch.
2. Inspect the Warning Triangle: Cells with numbers stored as text often display a small green triangle in the upper-left corner.
3. Use the TYPE Formula:
   • In an empty cell, enter =TYPE(A2) (where A2 is your lookup value).
   • In another cell, enter =TYPE(B2) (where B2 is the matching cell in your lookup table).
   • Result 1: Numeric value.
   • Result 2: Text value.
   • If one cell returns 1 and the other returns 2, a data type collision is confirmed.

---

Step 2: Choose Your Solution

You can resolve this issue at the formula level (temporary, non-destructive) or at the source data level (permanent cleanup).

Method A: Formula-Level Conversion (Fastest)

If you cannot modify the source lookup table because it is linked to an external data feed, modify your formula so that it converts the lookup number into text on the fly:

• For XLOOKUP:=XLOOKUP(A2&"", B2:B100, C2:C100, "Not Found")
• For VLOOKUP:=VLOOKUP(A2&"", B2:E100, 4, FALSE)
• For INDEX / MATCH:=INDEX(C2:C100, MATCH(A2&"", B2:B100, 0))

Appending &"" converts any number in A2 into its text equivalent without changing the visual output.

Method B: Text to Columns (Best for Source Data)

If you have edit access to the source data, converting the text column to real numbers prevents downstream lookup issues across all workbooks:

1. Highlight the column containing the numbers stored as text.
2. Navigate to the Data tab on the Ribbon.
3. Click Text to Columns.
4. In the wizard, keep Delimited selected and click Finish immediately.

Excel forces a re-evaluation of every cell in the range, converting valid text digits back into standard numbers.

Method C: Bulk Error Correction

If the green diagnostic flags are visible:

1. Select the entire range of numbers stored as text.
2. Click the yellow warning icon that appears next to the top cell.
3. Click Convert to Number.

For larger sheets where warnings are suppressed, refer to The “Error Checking” Dialog: Fixing errors in bulk.

---

What If the Fix Still Returns #N/A?

If applying A2 & “” still produces #N/A, walk through these diagnostic checks:

1. Check for Leading or Trailing Whitespace: Database exports often pad codes with spaces (e.g., “10452 “). Use TRIM: codeExcel =XLOOKUP(TRIM(A2) & "", D2:D100, F2:F100)
2. Check for Non-Breaking Spaces: Web exports frequently contain ASCII character 160 ( ), which TRIM ignores. Clean the string using SUBSTITUTE: codeExcel =XLOOKUP(SUBSTITUTE(A2, CHAR(160), "") & "", D2:D100, F2:F100)
3. Verify the Opposite Condition: If your lookup value was generated using text-manipulation functions like LEFT or MID, your lookup value is actually text and the table contains numbers. In that case, follow the steps in #N/A when searching Text stored as Numbers.
4. Isolate with Evaluate Formula: Step through calculation sub-routines using the built-in audit engine. See Using the “Evaluate Formula” tool to trace the root of #VALUE!.

If your workbook has the opposite setup, where your search key is text and your database contains true numbers, read #N/A when searching Text stored as Numbers. For a comprehensive index of lookup breaks, visit Troubleshooting #N/A Errors in Excel: The Ultimate Lookup Diagnostic Guide.