VLOOKUP Not Working? Check Number Formats

Yes—VLOOKUP can fail when one lookup value is a number and the matching value is text, even if both look identical on the worksheet. Changing a cell’s display format alone usually does not fix that mismatch: convert the underlying values to the same type, then use an exact-match lookup such as =VLOOKUP(E2,$A$2:$B$20,2,FALSE).

Why number formats can affect VLOOKUP

Excel keeps a value and its display format as separate things. A cell containing the number 123 can be displayed as 123, $123.00, or 00123, depending on its format. The stored value remains 123. A cell containing the text “123” may look exactly the same as a numeric 123, but Excel treats the two values as different types in an exact lookup.

This distinction is the source of many VLOOKUP problems. Data copied from a website, exported from a database, or imported from a text file may contain numbers stored as text. The lookup value might be numeric while the first column of the lookup table is text, or the reverse. VLOOKUP then returns #N/A because it cannot find an exact match of the same type.

Number formatting itself is not normally a barrier. Formatting both cells as Number, General, or Currency changes how values appear; it does not necessarily convert text into numbers. Similarly, a custom format that displays leading zeros does not add those zeros to the stored numeric value. The important question is not just “What does the cell look like?” but “What value and type does Excel actually store?”

Check the lookup type and match setting

Start with the formula. VLOOKUP searches for the lookup value in the leftmost column of its table range, then returns a value from a column to its right. The fourth argument controls whether Excel seeks an exact or approximate match. Use FALSE for an exact match in most ordinary lookup tasks. If you omit that argument, VLOOKUP uses approximate-match behavior, which can return an unexpected result when the first column is not sorted appropriately.

For example, with the lookup key in E2, IDs in A2:A20, and the return values in B2:B20, use:

=VLOOKUP(E2,$A$2:$B$20,2,FALSE)

The formula searches column A for E2 and returns the corresponding value from column B. The dollar signs keep the table range fixed if you copy the formula down. They do not change how Excel compares the values.

Check the cell types directly. With the suspected lookup value in E2, this formula reports TRUE if E2 contains a number:

=ISNUMBER(E2)

With the suspected lookup value in E2, this formula reports TRUE if it contains text:

=ISTEXT(E2)

Run the same checks on a cell in the table’s first column, such as A2. If one check says the lookup key is numeric and the other says the table entry is text, you have found a likely cause. Also verify that the table range starts with the column containing the lookup keys. VLOOKUP cannot search a column to the right and return a value from a column to its left.

Convert numbers stored as text

If the values are meant to be ordinary numbers, convert both sides to numbers. In Microsoft 365 Excel for Windows, cells with numbers stored as text may show a green error indicator. Select the affected cells, click the warning icon, and choose Convert to Number when that option is available. You can also use Data > Text to Columns for a selected column, then choose Finish when the delimiter settings are appropriate; this often converts numeric text during the import process. Check a small sample before converting a large dataset, especially if the column contains codes rather than quantities.

A formula can convert text containing a valid number. With the text lookup key in E2, put this in a helper cell such as F2:

=VALUE(E2)

Then, with the converted lookup key in F2, numeric IDs in A2:A20, and return values in B2:B20, use:

=VLOOKUP(F2,$A$2:$B$20,2,FALSE)

VALUE is useful when the text represents a number Excel can interpret under your regional settings. It may not be suitable for product codes, account numbers, or other identifiers where the digits are labels rather than amounts. If converting the table is more practical than converting the lookup key, make a numeric helper column for the table and use that helper column as the leftmost column of the VLOOKUP range.

For a one-time cleanup, conversion tools can be quicker than formulas. For a recurring import, a helper column or a carefully checked import process is easier to repeat and audit. Keep the original data until you have confirmed that the converted values preserve the information you need.

Handle leading zeros and identifier codes carefully

Leading zeros need special care. If an ID is stored as the number 123 and formatted to display as 00123, Excel still stores 123. If the corresponding lookup-table ID is the text “00123”, the values are not interchangeable for an exact match. Simply applying a custom number format to both cells will not turn the numeric value into the text code.

First decide what the digits mean. If 00123 is an identifier with a fixed five-character structure, treat it consistently as text. If it is a quantity, leading zeros are usually just presentation and storing it as a number may be appropriate. Do not strip zeros from postal codes, account numbers, or other identifiers where they distinguish the actual code.

For a fixed-width, five-digit code, with a numeric ID in A2, this helper formula creates text with leading zeros:

=TEXT(A2,"00000")

With a numeric lookup key in E2, the same five-character convention can be applied to the lookup key:

=TEXT(E2,"00000")

Use the resulting text values on both sides of the exact lookup. The pattern must reflect the real code format. If codes have different lengths, or zeros are meaningful but lengths vary, converting the source to text without imposing a fixed width is safer. Formatting every ID to five digits just to make a formula work can silently alter the intended meaning of codes that are not actually five characters long.

Clean spaces and imported characters

Sometimes the values are both text—or both numbers—but one contains extra characters. A leading or trailing space can make text values look identical while preventing an exact match. Imported data may also contain nonbreaking spaces, which are visually similar to ordinary spaces and are not removed by every basic cleanup method.

With a text value in E2, this formula replaces nonbreaking spaces with ordinary spaces and trims extra ordinary spaces at the beginning and end, as well as repeated spaces between words:

=TRIM(SUBSTITUTE(E2,CHAR(160)," "))

If the source may also contain nonprinting characters, with the imported value in E2, use:

=TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," ")))

Apply equivalent cleanup to the lookup-table keys too; cleaning only the lookup value will not remove unwanted characters from the table. These formulas can change text, so compare the cleaned results with the originals before relying on them. For instance, spaces inside a name can be meaningful, and stripping characters indiscriminately can make distinct identifiers appear the same.

Another useful check is to compare the lengths of two text values. With the lookup key in E2, this returns its character count:

=LEN(E2)

If two entries look alike but have different lengths, hidden spaces or characters are a plausible explanation. This check does not identify which character differs, but it narrows the search.

Dates, percentages, and other look-alike values

Dates are stored by Excel as serial numbers and displayed with date formats. A genuine date and text that looks like a date can therefore behave differently in a lookup. If imported dates do not match, check whether both columns contain real Excel dates rather than one containing date-shaped text. Regional date conventions matter: a text value such as 04/05/2026 may be interpreted differently depending on settings, while a genuine date has an underlying serial value.

Percentages also have underlying numeric values. Excel stores 25% as 0.25 and uses formatting to display the percent sign. Text containing the characters “25%” is not the same stored value. Currency symbols and thousands separators can create a similar visual impression: a formatted number is still numeric, while a symbol typed into a text value may make that entry text.

When the lookup is about a code, do not assume it should be converted just because it contains digits. When it is about a measurement, date, or amount, consistent numeric values are often the right representation. Choose the type based on what the data means, not merely on which conversion makes the error disappear.

A reliable troubleshooting sequence

When VLOOKUP returns #N/A despite an apparent match, work through the causes in a consistent order. This avoids changing good source data unnecessarily.

Confirm that the fourth VLOOKUP argument is FALSE when you need an exact match.
Confirm that the lookup key is in the first column of the selected table range and that the column index points to the intended return column.
Check the data type on both sides with ISNUMBER or ISTEXT, and compare the underlying values rather than only their displayed formats.
Check identifiers for meaningful leading zeros. Decide whether each side should be numeric or text before converting anything.
Look for spaces and imported characters; compare text lengths and clean both the lookup value and the table keys if appropriate.
Test the formula on a known matching row, then fill it down only after the result is correct.

For example, suppose A2 contains the numeric value 102, B2 contains “North”, and E2 contains the text value “102”. Both IDs may display as 102, but an exact VLOOKUP can return #N/A. Converting E2 to a number with VALUE, or consistently converting the table IDs to text, resolves the type mismatch. If the key is actually a five-character code such as 00102, preserve that structure as text instead of treating it as the number 102.

Avoid wrapping the formula in IFERROR until you understand the cause. It can make a worksheet look cleaner, but it also hides errors such as a misspelled key, an incorrect table range, or an unintended conversion. During troubleshooting, seeing the original #N/A is useful evidence.

When another lookup function may help

VLOOKUP remains useful when the lookup key is in the first column of a table and the answer is to its right. If your Excel edition includes XLOOKUP, it can search in either direction and lets you specify an explicit value for not-found results. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It does not remove the need to make lookup values consistent: a text-number mismatch can still prevent a match.

With lookup keys in A2:A20, results in B2:B20, and the key in E2, an XLOOKUP formula is:

=XLOOKUP(E2,A2:A20,B2:B20,"Not found")

Changing functions is not a substitute for checking data types, extra characters, or whether the key is genuinely present. If you need to keep using VLOOKUP for compatibility, a consistent helper column is often the most transparent fix.

Frequently Asked Questions

Does changing a cell from Text to Number fix VLOOKUP?
Not always. Changing the number format changes display, but may leave text stored as text. Use a real conversion, such as Convert to Number, Text to Columns, or a suitable helper formula, and confirm the type on both sides.

Why does VLOOKUP return #N/A when the values look identical?
They may differ in type, contain hidden spaces or characters, or have different underlying values concealed by formatting. Check text versus number, leading zeros, and the exact-match argument before assuming the visible display tells the whole story.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE when you need an exact match, which is the common choice for names, IDs, and codes. TRUE requests approximate matching and depends on the first table column being sorted in ascending order. Omitting the fourth argument also selects approximate matching.

Can VLOOKUP match a number to a number stored as text?
An exact lookup can fail when one value is numeric and the other is text, even if both display the same digits. Convert one side consistently, choosing text for identifiers whose formatting—such as leading zeros—is meaningful.

Will formatting both cells as General fix a mismatch?
No. General affects how Excel displays a value; it does not reliably convert a text entry into a number or a number into text. Convert the underlying data to the intended type instead.

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.