Excel VLOOKUP Not Working With Numbers? Fix the Match

When VLOOKUP cannot find a number, the most common cause is that one value is a real number and the other is a number stored as text. Make both lookup values the same underlying type, then use an exact-match VLOOKUP with FALSE; number formatting alone does not convert text into a number.

How VLOOKUP matches numbers

VLOOKUP searches for a value in the first column of a selected range and returns a value from another column in the same row. In Microsoft Excel for Microsoft 365 on Windows, its basic syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The last argument controls whether Excel looks for an exact or approximate match.

For example, assume product IDs are in A2:A100 and product names are in B2:B100, with the ID to find in E2. With IDs in A2:A100, names in B2:B100, and the lookup ID in E2, use =VLOOKUP(E2,$A$2:$B$100,2,FALSE). The formula searches column A and returns the corresponding name from column B. FALSE requests an exact match; 0 has the same effect.

Excel distinguishes a numeric value such as 417 from text that looks like 417. The cells may look identical on screen, but they are different data types, so an exact lookup can return #N/A. Conversely, two actual numbers can match even if one is displayed with commas or a different number of decimal places: display formatting does not alter the stored value.

Check the match type and lookup column

Before changing data, check the formula itself. If you omit the fourth argument, VLOOKUP uses approximate matching. That behavior can return an unexpected result when the lookup column is unsorted, or when you intended to find an exact ID. For codes, account numbers, names, and most ordinary lookups, explicitly use FALSE or 0.

Approximate matching is useful for sorted thresholds, such as assigning a rate based on a score band, but the first column must be sorted in ascending order for reliable results. It is not the right setting for finding a particular invoice number. Also confirm that the values you want to search are in the leftmost column of the selected table range. VLOOKUP cannot search a column to the right and return a result from a column on its left.

Check that the fourth argument is FALSE or 0 when you need an exact match.
Make sure the lookup column is the first column in the table_array.
Confirm that col_index_num points to the intended return column and is no larger than the width of the range.
Use dollar signs in the range, as in $A$2:$B$100, if you will copy the formula to other rows.

A wrong column number can produce a valid-looking but incorrect answer; a column number larger than the selected range can produce #REF!. These are formula setup problems rather than number-type mismatches, so it is worth checking them before editing source data.

Find out whether a value is text or a number

Cell alignment is only a clue: Excel often aligns numbers to the right and text to the left by default, but custom formatting and manual alignment can make that clue unreliable. A quick check is to select the cell and look at the formula bar, then inspect the Number Format control on the Home tab. Neither appearance nor a format such as General proves that the underlying value has the needed type.

For a more direct test, use Excel’s ISNUMBER function in an empty cell. With the value being checked in A2, enter =ISNUMBER(A2). It returns TRUE if A2 contains a numeric value and FALSE if it contains text, including text digits such as “417.” Test the lookup value and a representative key from the source column. If one test is TRUE and the other FALSE, the mismatch is a strong candidate for the failure.

Leading apostrophes, imported CSV files, copied web data, and systems that export IDs as text commonly create this situation. A green triangle in a cell may also indicate a number stored as text, though it is not shown in every configuration. Dates can have the same issue: Excel stores a genuine date as a serial number, while a date-looking text string remains text. They may appear the same but fail to match.

Convert text digits into real numbers

If the values are quantities or numeric keys and should genuinely be numbers, convert the source data and the lookup value consistently. A useful option is Excel’s Convert to Number warning when the affected cells show an error indicator. Select the cells, click the warning icon, and choose Convert to Number. The warning and its options depend on the data and Excel settings, so it may not appear for every range.

You can also select a column and use Data > Text to Columns, then complete the wizard without changing the delimiter settings when no splitting is intended. This often converts imported number-text values. Another option is to copy a cell containing 1, select the target range, and use Paste Special > Multiply; test on a copy first, because this changes the selected values. Menu wording or placement can vary slightly by Excel build.

For a formula-based helper column, with the text digits in A2, enter =VALUE(A2). VALUE converts text that Excel recognizes as a number into a numeric value. Fill the helper formula down, then use the converted helper column as the lookup key or copy and paste the results as values over a suitable destination. If the source includes currency symbols, unusual separators, or nonbreaking spaces, clean or interpret those characters first; VALUE depends on what Excel recognizes under the computer’s regional settings.

For text containing ordinary extra spaces or nonbreaking spaces, with the imported value in A2, use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))). SUBSTITUTE changes nonbreaking spaces to ordinary spaces, TRIM removes extra ordinary spaces, and VALUE attempts the numeric conversion. This will not repair every kind of imported character or malformed value, so check the result before using it for a lookup. If the cleaned value is an identifier with meaningful leading zeros, do not convert it to a number.

Keep identifiers with leading zeros as text

Not every digit string should become a number. A product code such as 00417 may be an identifier, not a quantity. If Excel converts it to the number 417, the leading zeros disappear. Applying a custom number format can make a numeric 417 display as 00417, but the underlying value is still 417. That may be fine for display, but it does not make it equivalent to the text string “00417” in every lookup.

For IDs whose zeros matter, store both lookup keys as text and make their characters consistent. A reliable approach is to clean and standardize the source system’s IDs before the data reaches Excel, rather than guessing how many zeros to restore later. If one list stores 417 and another stores 00417, first establish whether they refer to the same identifier and what its official format is. Do not remove zeros or convert IDs blindly when the data is used for records, inventory, or reconciliation.

Spaces are another hidden difference. A key may contain a leading space, a trailing space, or a nonbreaking space copied from a web page. The text can look identical while the character sequences differ. TRIM removes ordinary extra spaces but does not remove every invisible character; CLEAN removes certain nonprinting characters, but it does not replace nonbreaking spaces by itself. For that reason, inspect and normalize representative values rather than assuming a displayed match is an exact match.

Diagnose #N/A and other errors

#N/A usually means an exact match was not found, but it does not identify the cause. Check data type, spaces, punctuation, leading zeros, and whether the value exists in the lookup column at all. Also check that the formula points to the right sheet and range. A formula copied down may have shifted its range if it was not anchored with dollar signs.

To separate a genuine missing key from a formula problem, search the lookup column directly with Ctrl+F on Windows. On Mac, use Command+F. Search for the exact displayed key and inspect the cell that Excel finds. A manual search is useful, but it does not prove the underlying types match; two cells that look the same can still differ as text and number.

Once the underlying issue is fixed, you may want a friendlier message for keys that are genuinely absent. With lookup IDs in A2:A100, return values in B2:B100, and the key in E2, use =IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"No match"). IFNA replaces only an #N/A result; it does not conceal other errors such as #REF!. Avoid wrapping a lookup in error handling before diagnosing it, because that can hide a broken range or a mismatched key.

If you see #VALUE! or #REF! instead, inspect the formula arguments and the selected table width. The column index must be a positive number that identifies a column inside the chosen range. If the formula returns a result but it is wrong, check for duplicate keys: VLOOKUP returns the first matching row it encounters, not every matching row.

When to use a different lookup method

VLOOKUP remains useful for familiar, straightforward tables, especially when the lookup key is in the first column and you want a result to its right. It can be fragile when columns are inserted or rearranged because the return column is specified by a number. If the table layout makes a VLOOKUP awkward, Microsoft 365 includes XLOOKUP, which can search one range and return a result from a separate range. It still cannot make inconsistent text and numeric keys equivalent automatically, so the data-type issue must still be addressed.

With keys in A2:A100, results in B2:B100, and the search key in E2, an exact XLOOKUP can be written as =XLOOKUP(E2,A2:A100,B2:B100,"No match"). XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel releases such as Excel 2019. If a workbook must be opened by people using older versions, confirm function compatibility before replacing VLOOKUP.

For large imported datasets, it may be better to fix the column types during import or in Power Query rather than repeatedly applying helper formulas. In Excel for Microsoft 365 on Windows, Power Query is available from the Data tab through Get & Transform Data options; available commands depend on the selected source and workbook. The key principle is the same: set both matching columns to compatible types and verify that IDs retain any meaningful formatting. On Mac and Excel for the web, available import and transformation features differ by version, so do not assume every Windows Power Query command is present.

Frequently Asked Questions

Why does VLOOKUP return #N/A when both values look like numbers?
One value may be a number and the other text, or one may contain an invisible space or a different character. Test both cells with ISNUMBER, inspect the source, and standardize the keys without removing meaningful leading zeros.

Does changing the cell format fix VLOOKUP?
No. A number format changes how a value is displayed, not whether it is stored as text or as a number. Convert the value itself when it should be numeric, or keep both keys as consistent text when they are identifiers.

Should I use TRUE or FALSE for VLOOKUP?
Use FALSE, or 0, for an exact lookup such as an ID or product code. TRUE or an omitted fourth argument requests approximate matching and is appropriate only for use cases designed around an ordered range, such as thresholds.

Can VLOOKUP find a number stored as text?
It can match text to text when the character strings are identical, and numbers to numbers when their numeric values are equal. A text value and a numeric value that merely look alike may not match, so make both sides the same type.

Why does VLOOKUP return the wrong result instead of #N/A?
The formula may be using approximate matching, the return-column index may be wrong, or the lookup column may contain duplicate keys. Set an exact match for IDs and inspect the first matching row and the formula’s selected range.

Does XLOOKUP solve number-versus-text mismatches?
No. XLOOKUP offers a more flexible way to define lookup and return ranges, but it does not automatically standardize text and numbers. Normalize the keys first, and check that everyone opening the workbook has a version that supports XLOOKUP.

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.