VLOOKUP Not Working? Fix Numbers Stored as Text

Yes—VLOOKUP can return #N/A when the lookup value is a number but the matching value in the table is stored as text, or vice versa. Make both sides the same type: for a quick check, convert the lookup value or the table’s key column, then use VLOOKUP with exact match set to FALSE.

Why numbers stored as text break VLOOKUP

Excel distinguishes between a numeric value such as 1042 and text that looks like a number, such as “1042”. They may appear identical in a cell, but an exact-match lookup can treat them as different values. This often happens when data comes from a CSV, a website, an accounting system, or a database export. An apostrophe typed before a value, such as ‘1042, also tells Excel to store it as text.

For example, suppose A2 contains the numeric value 1042, while D2 contains the text value “1042” and E2 contains Widget. With the lookup value in A2 and the table in D2:E100, this exact-match formula can return #N/A because the two key values have different types: =VLOOKUP(A2,$D$2:$E$100,2,FALSE). The last argument, FALSE, is important: it requests an exact match rather than an approximate one.

Changing how a cell looks is not the same as changing its value. Applying the Number format to text that already exists does not convert it into a number. Likewise, changing the format of a numeric cell to Text does not necessarily turn its existing value into text; the value may need to be re-entered or converted. Fix the underlying data type, not just the display format.

How VLOOKUP handles the lookup value

VLOOKUP searches for a value in the leftmost column of its table range and returns a value from a column to its right. Its basic structure is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). For most ID, invoice, product-code, and name lookups, use FALSE (or 0) for the final argument to require an exact match.

If you leave the final argument out, VLOOKUP uses approximate matching. That can produce a result that is not the row you intended, particularly when the first column is not sorted. Approximate matching is designed for ordered ranges such as tax bands or commission thresholds, not for ordinary unique-key searches. Including FALSE makes the intended matching behavior clear, but it cannot make a number equal to text.

VLOOKUP also searches only the first column of the selected table range. If your key is in column D and the return value is in E, the range should start at D. The column number 2 then means “return the second column of that selected range,” not worksheet column B. A wrong range or column number can cause an error or an unexpected result that looks like a type problem.

Check whether the two values have different types

Start by selecting the lookup cell and a suspected matching cell in the source table. The formula bar can reveal an apostrophe or unexpected spaces, though it does not always make the data type obvious. Excel may show a green triangle for a number stored as text, but indicators depend on settings and may not appear for every cell.

A practical test is to inspect the values individually and try a controlled conversion in a spare column. In a worksheet where the suspected key is in D2, the cell layout for this test is D2 containing text digits such as “1042”. This formula converts a valid numeric text value to a number and returns 1042: =VALUE(D2). If the source contains nonnumeric characters, VALUE can return #VALUE! instead, which is useful evidence that the data needs cleaning rather than simple conversion.

Look for mixed formats: some entries may be numbers while others are text in the same key column.
Check for leading or trailing spaces, nonbreaking spaces, or other hidden characters.
Consider whether leading zeros are meaningful. Product code 001042 may not be interchangeable with numeric 1042.
Confirm that the lookup range starts with the key column and includes the return column.

Do not rely on alignment alone. Excel commonly aligns numbers to the right and text to the left by default, but users can change alignment, so it is only a clue.

Convert the table’s text values to numbers

If the keys are genuinely numeric quantities or numeric IDs where leading zeros do not matter, converting the lookup column to numbers is often the cleanest permanent repair. Make a copy of the data first if the original import must be preserved. Select the affected cells and use the warning icon’s Convert to Number option if Excel offers it.

Another built-in route is Text to Columns. Select the text-number column, choose Data > Text to Columns, keep the default Delimited option, and select Finish. For a straightforward column of digit strings, Excel commonly converts the values to numbers. Check the result before using it, especially if the column contains mixed content, long identifiers, or leading zeros. On Mac, the Text to Columns command is also on the Data tab, though the dialog appearance can vary by Excel release.

You can also make a helper column. With text numbers in D2:D100 and their corresponding return values in E2:E100, put a numeric conversion in F2 and copy it down; put the corresponding return values in G2:G100. F2 will contain 1042 if D2 contains the text “1042”. Then, with the lookup value 1042 in A2, this formula searches the numeric helper column and returns Widget from the matching row: =VLOOKUP(A2,$F$2:$G$100,2,FALSE).

For a small, clean range, Paste Special can also coerce text digits: enter 1 in an empty cell, copy it, select the text-number cells, and use Home > Paste > Paste Special > Multiply. This changes the selected values, so use it only when conversion is intended. It is not suitable for codes whose leading zeros carry meaning.

Convert the lookup value instead

If the table’s key column is already numeric and only the lookup input is text, convert the lookup input. With text digits in A2 and numeric keys in D2:D100, the lookup value can be converted inside VLOOKUP; if A2 contains “1042” and the matching row returns Widget, this formula returns Widget: =VLOOKUP(VALUE(A2),$D$2:$E$100,2,FALSE). This is convenient for a one-off lookup, but a helper column can make repeated lookups and data checks easier to understand.

If the table deliberately stores keys as text, convert a numeric lookup value to text instead. With numeric 1042 in A2 and text keys such as “1042” in D2:D100, this formula returns Widget when that key is present: =VLOOKUP(TEXT(A2,"0"),$D$2:$E$100,2,FALSE). The format string “0” is appropriate for ordinary whole numbers, but it does not restore leading zeros that have already been lost. For fixed-width codes, use the correct width only when that format is part of the data definition.

It is usually better to standardize the source data than to embed a conversion in every lookup. Inline conversions can conceal inconsistent records, and a conversion formula may fail when a cell contains labels, punctuation, or an unexpected decimal or date format. For a shared workbook, a clearly named helper column documents what has been normalized.

Preserve IDs and other values that look numeric

Not every string of digits should be converted to a number. Account numbers, postal codes, telephone numbers, SKU codes, and some employee IDs are identifiers, not quantities. Their text representation may preserve leading zeros or exceed the precision Excel can reliably retain for long numeric entries. Converting an identifier such as 001042 to a number changes its displayed value to 1042 and may make it a different key.

For identifiers, choose text as the standard type and convert numeric lookup inputs to text, or import both key columns as text from the start. If a source system has already removed leading zeros, determine the required width or recover the original identifier from the authoritative source; formatting the cell cannot infer missing digits. When importing a CSV, review the import options and set identifier columns to Text where available rather than assuming Excel will interpret every column correctly.

Long digit strings need special care. Excel numeric values have limited precision, so a long code entered as a number may lose digits. Storing such values as text prevents arithmetic treatment and preserves the characters. That is usually the right choice for keys, even though the values look numeric.

Other reasons an exact-match lookup can fail

If both sides are the same type and the result is still #N/A, inspect the characters and the range. A trailing space in a source value, for example, makes “1042 ” different from “1042”. Data copied from web pages may contain a nonbreaking space, which a basic TRIM operation may not remove. Other causes include a misspelled key, a lookup range that omits the matching row, or a value that differs by punctuation.

Check these common issues before rewriting the lookup:

Make sure the search column is the first column in table_array.
Confirm that the chosen table range includes the target row and the return column.
Use FALSE for an exact match unless approximate matching is genuinely intended.
Compare the actual values, not just their displayed formats; check for spaces, hyphens, and inconsistent decimal values.
When copying a formula down, use dollar signs on a fixed lookup range so it does not shift unexpectedly.

A result that is wrong but not #N/A can point to a different issue. For example, an incorrect col_index_num may return a value from the wrong column. Approximate match on unsorted data may return an unintended row. Diagnose the error type and formula arguments rather than assuming every VLOOKUP problem is caused by text numbers.

When to use a different lookup method

For a workbook in Microsoft 365, XLOOKUP can be easier to read because it separates the lookup range from the return range and can specify what to show when there is no match. It still needs compatible key values: replacing VLOOKUP with XLOOKUP does not automatically solve a number-versus-text mismatch. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

VLOOKUP remains useful when a workbook must work with older Excel versions and the return column is to the right of the lookup column. INDEX and MATCH can look in either direction, but they also require the lookup value and searched keys to be consistent. Whichever function you choose, standardizing keys at import or in a documented helper column is more dependable than hoping a different lookup function will coerce inconsistent data.

The steps and formulas here apply to Excel for Microsoft 365 on Windows. Excel for Mac has the same core VLOOKUP and VALUE behavior; ribbon placement and dialog details can differ slightly. Excel for the web also supports VLOOKUP, but some conversion dialogs or import controls may be more limited than in the desktop app.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the numbers look the same?
One value may be a number and the other text, or one may contain a hidden space or character. Confirm the type and contents of both keys, then convert or clean them so they match.

Does formatting a text number as Number fix VLOOKUP?
No. Number formatting changes how a value is displayed; it does not reliably convert existing text into a numeric value. Use Convert to Number, Text to Columns, or a conversion formula when numeric conversion is appropriate.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is right for most lookup keys. TRUE or an omitted final argument requests approximate matching and is intended for suitably ordered data such as ranges or thresholds.

How do I match product codes with leading zeros?
Keep both lookup and table codes as text, including the zeros. If Excel has already converted a code and removed its zeros, restore the correct code from the source or apply a known fixed width only when that rule is certain.

Will XLOOKUP fix a number stored as text?
No. XLOOKUP can replace VLOOKUP in supported Excel versions, but the lookup value and lookup array still need compatible values. Normalize the keys first, then use the function that best fits the workbook.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.