Excel VLOOKUP Numbers Stored as Text: Fixes

Formulas & functions · Updated November 22, 2026

The direct answer: when VLOOKUP returns #N/A because your lookup value is a number but the data is stored as text (or vice versa), the fix is to make both sides the same data type using the VALUE or TEXT function, or by using XLOOKUP with the match_mode argument. For example, if your lookup value is in A2 and the first column of your table contains text-formatted numbers, use =VLOOKUP(TEXT(A2,"0"), Table1, 2, FALSE).

Why Excel Treats Numbers and Text Differently

Excel stores data in two distinct ways for numbers: as actual numeric values (which can be calculated) and as text strings (which look like numbers but are not). When you type a number into a cell, Excel aligns it to the right by default and stores it as a number. When you type an apostrophe before a number (e.g., '123), or import data from a CSV or another system, Excel may store it as text, aligning it to the left. VLOOKUP performs an exact match by default (when the fourth argument is FALSE), and it treats a number and its text equivalent as different values. This is a common source of #N/A errors, especially when dealing with imported data, IDs, or phone numbers.

How VLOOKUP Matching Works

VLOOKUP searches for a lookup value in the first column of a table array. When the fourth argument is FALSE (or 0), it looks for an exact match. The comparison is case-insensitive for text but is strict about data types: a number like 123 is not considered equal to the text string “123”. When the fourth argument is TRUE (or omitted), VLOOKUP assumes the first column is sorted and performs an approximate match, which can return incorrect results if the data type mismatch exists. In practice, most users use FALSE to avoid false matches, but this makes the data type issue more visible.

Methods to Fix the Data Type Mismatch

There are several ways to resolve the mismatch, depending on which side is text and which is numeric.

  • Convert the lookup value to text: If your table’s first column contains text-formatted numbers, wrap the lookup value with TEXT. For example, =VLOOKUP(TEXT(A2,"0"), Table1, 2, FALSE) converts a number in A2 to text with no decimal places. Adjust the format code to match your data (e.g., “0.00” for two decimals).
  • Convert the table column to numbers: If your lookup value is numeric but the table column is text, use VALUE to convert the table’s first column. You can’t do this directly inside VLOOKUP without an array formula, so it’s easier to fix the source data. Select the column, use the error indicator (green triangle) and choose “Convert to Number”, or use Text to Columns (Data > Text to Columns, then Finish).
  • Use XLOOKUP with match_mode: In Excel 2021 or Microsoft 365, XLOOKUP has a fifth argument for match mode. Setting it to 0 (exact match) still requires the same data type, but you can use -- to coerce text to numbers. For example, =XLOOKUP(A2, --Table1[Column1], Table1[Column2]) converts the table column to numbers on the fly. Note that this works only if all entries in that column are numeric text.
  • Use SUMPRODUCT or INDEX/MATCH with coercion: A classic workaround is =SUMPRODUCT((--Table1[Column1]=A2)*Table1[Column2]) but this returns a sum, not a unique match. Better: =INDEX(Table1[Column2], MATCH(A2, --Table1[Column1], 0)) as an array formula (Ctrl+Shift+Enter in older versions, but in Microsoft 365 it works normally). The double unary -- converts text numbers to real numbers.

How to Identify Which Cells Are Text

You can quickly spot text-formatted numbers by looking at the cell alignment (left vs right), but that’s not always reliable. The ISTEXT function can test a cell: =ISTEXT(A2) returns TRUE if A2 is text. The ISNUMBER function returns TRUE for numeric values. Also, the green triangle in the top-left corner of a cell indicates an error: “Number Stored as Text”. If you select the cells, you’ll see a warning icon that offers to convert them. For a whole column, you can use the Go To Special feature (Home > Find & Select > Go To Special > Constants, then uncheck Numbers) to select text cells only.

Common Problems and Pitfalls

One common pitfall is using VALUE on a cell that already contains a number, which returns the same number, so it’s safe. However, VALUE fails if the text has non-numeric characters like spaces or currency symbols. Another issue: if your table column contains a mix of real numbers and text numbers, converting the whole column might cause errors. Also, when you use the TEXT function, the format code must exactly match the text representation in the table. For example, if the table has “00123” as text, you need TEXT(A2,"00000") to preserve leading zeros. If you use a simple “0”, you’ll get “123” and the match fails. Additionally, approximate match (TRUE) can return wrong results if the first column is not sorted correctly; always use FALSE unless you have a specific reason.

Alternatives to VLOOKUP

VLOOKUP has limitations: it can only look to the right, and it requires the lookup column to be the first column in the table. INDEX and MATCH are more flexible and can handle data type mismatches with coercion. XLOOKUP is the modern replacement, available in Excel 2021 and Microsoft 365, and it allows left lookups and has a built-in match mode. For example, =XLOOKUP(A2, Table1[Column1], Table1[Column2], "Not found", 0) does an exact match. If you need to ignore data types entirely, you can use XLOOKUP(A2, --Table1[Column1], Table1[Column2]) as mentioned. Another alternative is FILTER (in Microsoft 365) to return multiple matches: =FILTER(Table1[Column2], Table1[Column1]=A2) but this also requires same data type unless you coerce with --.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though I can see the value?
This is almost always a data type mismatch. Check if the lookup value or the table column is stored as text. Use ISTEXT to verify. The fix is to convert one side to match the other.

How do I convert text to numbers in Excel?
Select the column, click the warning icon (if present) and choose “Convert to Number”. Alternatively, use Text to Columns (Data > Text to Columns, then click Finish) or multiply by 1 with a helper column: =A2*1 and copy down.

Can I use VLOOKUP with leading zeros?
Yes, but you must format the lookup value as text with the same number of digits. For example, if IDs are stored as “00123”, use =VLOOKUP(TEXT(A2,"00000"), Table1, 2, FALSE).

Does XLOOKUP solve this problem automatically?
No, XLOOKUP still distinguishes between text and numbers. However, you can use the -- operator to coerce text to numbers, or use VALUE on the lookup value, to make the match work.

What if my data has trailing spaces?
Trailing spaces are a common cause of #N/A errors. Use the TRIM function to remove extra spaces: =VLOOKUP(TRIM(A2), Table1, 2, FALSE). Also, ensure the table column has no extra spaces; use TRIM on that column if needed.

Is there a way to make VLOOKUP ignore data types?
Not directly. VLOOKUP is strict about data types. You can use an array formula with -- to coerce, but the simplest approach is to clean your data first. In Microsoft 365, you can use XLOOKUP with -- on the lookup array, as shown earlier.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.