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
VALUEto 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,
XLOOKUPhas 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
- House Construction Budget Excel Template: How to Build One
- Excel Template for Income Expense Tracking: How to Build One
- Excel Template for Family Budget: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUBSTITUTE Shortcut: Function and Replace KeysExcel has no dedicated keyboard shortcut for the SUBSTITUTE function. To replace text in cells with a formula,…
- Excel SUMPRODUCT with IF: How to Use ItThe direct answer: Excel’s SUMPRODUCT function can perform conditional summing without needing an IF function, by multiplying a…
- PMT in Excel: Calculate Loan Payments Step by StepTo calculate a fixed loan payment in Excel, use the PMT function: enter the interest rate per payment…
- Excel: How to Hide Columns to the Right of Your DataTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
- Excel FILTER Function for Text: Examples and TipsWith records in A2:C6 and the text to match in B2:B6, use =FILTER(A2:C6, B2:B6="North", "No matching rows") to…
- Excel Conditional Formatting Due Date FormulaThe core formula for highlighting overdue dates in Excel is =A2<TODAY() applied to a range starting at A2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.