VLOOKUP Not Working on Numbers? Fix the Match

When VLOOKUP will not match a number, the most common cause is that one value is stored as a number and the other as text—even if both look identical in the cell. Check the lookup range and use an exact match, then make the lookup value and the first column of that range the same underlying type.

What VLOOKUP expects to find

VLOOKUP searches for a value in the leftmost column of a table and returns a value from a column to its right. In Microsoft Excel for Microsoft 365 on Windows, its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The fourth argument controls the match: use FALSE, or 0, for an exact match. If you omit it, VLOOKUP uses approximate matching, which is not a safe default for most lists of IDs, product codes, or names.

With the lookup number in A2, numeric keys in D2:D4, and corresponding labels in E2:E4, this formula asks for an exact match and returns the label in the second column of the table:

=VLOOKUP(A2,$D$2:$E$4,2,FALSE)

The table range must start with the column being searched. VLOOKUP cannot search in E and return a value from D, for example. The column number is counted from the left edge of the selected range, so 2 means the second column of D:E. These structural details can cause a formula to fail or return an unexpected value even when the numbers themselves are fine.

Number stored as text: the most common mismatch

Excel distinguishes the numeric value 102 from the text string “102.” A cell’s display format is not the same as its stored value: applying a Number format to text does not convert the text into a number. This often happens when data is pasted from a website or imported from a CSV, or when one list has been entered manually and another came from a system export.

For example, suppose A2 contains the numeric value 102, while D2 contains the text “102” and E2 contains “North.” With the layout of A2 as the lookup value and D2:E4 as the lookup table, an exact-match lookup can return #N/A because the key types differ:

=VLOOKUP(A2,$D$2:$E$4,2,FALSE)

Changing the cell’s appearance to General or Number may not fix this. To inspect a value, select the cell and look at the formula bar; a green triangle may also flag a number stored as text. For a direct type check, with the value to inspect in A2, use:

=ISTEXT(A2)

TRUE means the cell contains text, while FALSE means it is not text. A corresponding numeric check can help distinguish a real number from a string that only looks like one. Check both the lookup cell and a representative key in the table rather than assuming that an entire column has a consistent type.

Convert the values without changing the meaning

Choose which representation is correct before converting anything. For quantities such as a count or a price, numeric values are usually appropriate. For identifiers such as account numbers, postal codes, or product codes, text may be better because the characters are labels, not amounts to calculate. Leading zeros can matter: the numeric value 00123 becomes 123, while the text “00123” preserves all five characters.

If A2 contains a number written as text and D2:D4 contain real numbers, convert the lookup value. With A2 holding text “102,” D2:D4 holding numeric keys, and labels in E2:E4, this formula converts A2 to a number before looking it up:

=VLOOKUP(VALUE(A2),$D$2:$E$4,2,FALSE)

If the table keys are text instead, converting only A2 to a number will not solve the mismatch. One practical option is a helper lookup table. Suppose D2:D4 contains text-form numbers and E2:E4 contains the labels. In H2, convert the first key; in I2, copy the matching label. Fill both formulas down through row 4:

=VALUE(D2)

=E2

With the number to find in A2 and the converted keys and labels in H2:I4, use this exact-match lookup:

=VLOOKUP(A2,$H$2:$I$4,2,FALSE)

For a simple column where every entry is a number stored as text, Excel’s Convert to Number option may also be available from the warning icon beside a selected cell. Another approach is Data > Text to Columns, completing the wizard with the appropriate data format. Make a copy first if the source values must be preserved. Do not convert identifier columns blindly: stripping leading zeros can make distinct codes indistinguishable.

Check spaces, hidden characters, and formatting

Text keys can contain spaces or nonprinting characters that are hard to see. A trailing space in “102 ” is enough to make it differ from “102.” TRIM removes ordinary extra spaces at the beginning and end of text, while CLEAN removes certain nonprinting characters. Neither is a universal repair: imported nonbreaking spaces, for example, may need to be replaced separately, and cleanup should be applied consistently to both the lookup value and the table keys.

Also consider whether the cells are numbers displayed with a custom format. A numeric value shown as 00123 may still be the number 123; the leading zeros can be display-only. That number will not necessarily match the text identifier “00123.” Decide whether the key is numeric or textual, then normalize both sides to the chosen form. Dates deserve the same care: real Excel dates are stored as serial numbers, while a date-looking string imported as text is not the same stored value.

Other reasons VLOOKUP can fail or return the wrong result

Approximate match was used accidentally. If the fourth argument is omitted or TRUE, VLOOKUP can return an approximate result. Approximate matching expects the first column to be sorted in ascending order; an unsorted list can produce misleading results. For ordinary exact key lookups, specify FALSE.
The key is not in the first column of the selected range. VLOOKUP searches only the leftmost column of table_array. Expand or rearrange the range so the key is first, or choose a lookup function that supports searching and returning from separate columns.
The range or return-column number is wrong. If the formula refers to D2:E4 but the key is in C, it cannot find that key. If the column number points to the wrong field, the formula may return the wrong value or #REF! if the number exceeds the range’s width.
The formula’s references moved. When copying a formula down, a range such as D2:E4 may shift. Dollar signs lock the range, as in $D$2:$E$4, while the lookup reference A2 can change to A3 on the next row.
There are duplicate keys. VLOOKUP returns the first matching row it encounters. If a key appears more than once, verify that the first occurrence contains the result you want, or use a method designed to return multiple matches.
The formula is showing a different error. #N/A usually means no match was found; #REF! often points to an invalid return-column number or broken reference. A displayed formula rather than a result can indicate that Show Formulas is enabled or that the cell is formatted as Text.

A practical troubleshooting sequence

Work from the formula outward rather than changing several columns at once. This helps identify the real cause and avoids altering source data unnecessarily.

Click the formula cell and verify the lookup value, table range, and return-column number. Confirm that the key is in the range’s first column.
Set the fourth argument to FALSE for an exact match. If the formula is copied down, check that the table range is fixed with dollar signs.
Compare the lookup cell with a key that should match. Check whether each is text or numeric, and look for leading zeros, spaces, and imported characters.
Test the issue on a small sample or a helper column. Convert only the values that need conversion, keeping a backup of the original data.
Check for duplicates and confirm that the return value is in the intended row. If the formula returns a result but it is not the expected one, the first matching duplicate or an incorrect column index may be responsible.
After the cause is fixed, remove any temporary helper formulas that are no longer needed, or keep them as part of a documented, repeatable cleanup process.

You can make a missing exact match easier to read without changing the lookup logic. With A2 as the lookup value and D2:E4 as the table, this formula displays a message when VLOOKUP returns #N/A:

=IFNA(VLOOKUP(A2,$D$2:$E$4,2,FALSE),"Not found")

Use this after investigating the mismatch, not instead of fixing it. A message can make a report clearer, but it can also conceal a data problem if many expected matches are being labeled “Not found.”

When XLOOKUP may be a better alternative

In Microsoft 365, XLOOKUP can search one range and return from a separate range, so the return column does not have to sit to the right of the key. It also uses exact matching by default, though specifying the match mode makes the intention clear. With the lookup value in A2, keys in D2:D4, and results in E2:E4, use:

=XLOOKUP(A2,$D$2:$D$4,$E$2:$E$4,"Not found",0)

This returns the corresponding result for an exact match, or “Not found” if there is none. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It does not automatically treat text “102” and numeric 102 as the same value, so type mismatches still need attention. VLOOKUP remains useful when a workbook must support older Excel versions or already uses established VLOOKUP formulas.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the numbers look the same?

The values may have different underlying types: one is a number and the other is text. They may also differ by a space, hidden character, or leading zero. Check both cells’ contents and normalize the lookup value and table key consistently.

Does formatting a text number as Number fix VLOOKUP?

Not necessarily. Number formatting changes how a value is displayed; it does not reliably convert text into a numeric value. Convert the stored value using an appropriate method, and check whether the key should remain text, especially for codes with leading zeros.

Should I use TRUE or FALSE in VLOOKUP?

Use FALSE when you need the exact key, which is the usual choice for names, IDs, and product codes. TRUE requests approximate matching and requires the first table column to be sorted in ascending order. Omitting the fourth argument also selects approximate matching.

Can VLOOKUP search for a number in any column?

No. VLOOKUP searches the leftmost column of the range you specify. Put the key column first in the selected range, or use XLOOKUP in a supported Excel version when the search and result columns are positioned separately.

Why does VLOOKUP return the wrong result when the key is duplicated?

VLOOKUP returns the first matching row in the selected table. Check the order of the rows and whether the key is unique. If you need every matching record rather than the first one, use a method that can return multiple results instead of expecting VLOOKUP to list them.

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.