VLOOKUP Not Working Even Though Values Match? Fixes

When VLOOKUP says values do not match, the cells often look identical but differ in data type, hidden characters, or the range being searched. Start by using an exact-match formula, then check the lookup column, cell contents, and table range; these checks solve most cases without rebuilding the workbook.

How VLOOKUP Finds a Match

VLOOKUP searches for a value in the leftmost column of a selected range and returns a value from another column in the same row. The column number is counted from the left edge of that range, not from the worksheet. For example, if the range starts in column A, column B is number 2. The final argument controls the match type: FALSE requests an exact match, while TRUE or an omitted argument allows an approximate match.

With the lookup value in E2, lookup keys in A2:A100, and results in B2:B100, use this exact-match formula:

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

The dollar signs keep the search range fixed if you copy the formula down. An exact match still does not mean a case-sensitive comparison: VLOOKUP does not distinguish uppercase from lowercase letters. Also, if the key appears more than once, VLOOKUP returns the first matching row, which can make a valid result look like the wrong one.

Check the Match Argument First

A missing fourth argument is a frequent cause of confusing results. In that case, VLOOKUP uses approximate matching. Approximate matching is intended for ordered ranges such as tax bands or grading thresholds; the first column should be sorted in ascending order. It returns the closest value that does not exceed the lookup value, rather than requiring an identical entry. In an unsorted list, it can return an unexpected result.

For an ordinary list of names, product codes, or invoice numbers, include FALSE explicitly. If you deliberately need approximate matching, use TRUE and make sure the first column is sorted as required. Do not use approximate matching as a way to make a failed exact match work: it can silently return a result from the wrong row.

Look for Text, Number, and Date Differences

Excel can display two cells the same way while storing different kinds of values. A number such as 458 may be stored as a numeric value in one cell and as text in another. The same issue occurs with codes that begin with zeros: 00127 as text is not the same stored value as the number 127, even if a number format makes the latter appear to have leading zeros. Exact lookup can fail when the lookup value and key column use different types.

Dates are another common trap. A real Excel date is stored as a number and displayed using a date format. A date typed or imported as text may look identical, but it is not the same value. Confirm the underlying types and standardize both sides of the lookup rather than changing only the display format. Formatting changes appearance; it does not convert text into a number or a real date.

Useful checks include changing the cell format temporarily to General, testing whether a value is stored as text, and examining the formula bar. Be cautious with identifiers: converting a code to a number can remove meaningful leading zeros. Decide whether the field is genuinely numeric or is an identifier that should remain text, then apply the same approach to both lookup and source values.

Remove Spaces and Invisible Characters

Imported data often contains leading or trailing spaces, repeated spaces, line breaks, or nonbreaking spaces copied from a web page. A cell containing ABC followed by a space may appear to match ABC but fail an exact lookup. CLEAN removes certain nonprinting characters, and TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces to single spaces. TRIM alone does not remove the nonbreaking space commonly represented by character code 160.

With the imported value in A2, this formula replaces nonbreaking spaces with ordinary spaces, removes certain nonprinting characters, and trims the result:

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

Put the cleaned result in a helper column, then use that cleaned column as the lookup key. Apply equivalent cleanup to the value being searched if it may contain the same characters. The formula is useful for ordinary text, but it is not a universal repair for every Unicode character or every language-specific spacing mark. If a match still fails, compare character lengths or inspect the source data more closely.

Verify the Lookup Column and Table Range

VLOOKUP can only search the leftmost column of its table_array. If the key is in column C but the selected range begins in column B, the formula will search B, not C. Select a range whose first column contains the keys. If you need to search a column to the right of the return column, VLOOKUP is the wrong fit unless you rearrange the data.

Also check that the return-column index is correct relative to the selected range. A value of 2 means the second column within table_array, not worksheet column B in every situation. If the index exceeds the number of columns in the range, Excel returns #REF!. If a formula copied down has moved its range, lock the references with dollar signs or use a carefully designed table reference. A result of 0 can mean the matched return cell is blank, rather than that the lookup failed.

Click the formula cell and inspect the colored range outlines in the formula bar, or select the references directly in the formula. Confirm that the key column is included, that the range includes the intended return column, and that the lookup value is not outside the data you selected. If new rows are added regularly, an Excel Table can help the data range expand as rows are added.

Use the Error Message as a Clue

Different errors point to different problems, so read the result rather than treating every failure as a mismatch:

#N/A: VLOOKUP did not find a match under the chosen match rules. Check exact versus approximate matching, data types, spaces, and whether the key is actually in the first column.
#REF!: The return-column number is outside the selected range, or a referenced column or cell has been removed.
#VALUE!: An argument may be invalid, such as a return-column index that is not a usable number.
A plausible but incorrect result: Check for approximate matching, duplicate keys, or a return-column index counted from the wrong starting column.

If you want a friendlier message for a genuine not-found result, wrap the lookup in IFNA. With E2 as the key, A2:A100 as the lookup column, and B2:B100 as the return column, use:

=IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found")

IFNA replaces #N/A with the message but leaves other errors visible, which helps keep structural problems such as #REF! from being hidden. Do not use an error-handling formula to conceal a lookup that is returning incorrect data.

A Practical Troubleshooting Order

Work through these checks in order so that you change one likely cause at a time and can tell what fixed the problem:

Set the fourth argument to FALSE for a conventional exact lookup.
Confirm that the first column of the selected range contains the lookup keys.
Check that the return-column index counts from the first column of that range.
Compare the stored types of the lookup value and source key: text, number, or date.
Check for spaces, line breaks, nonbreaking spaces, and other characters introduced during import.
Review the range boundaries and lock references if the formula has been copied.
Look for duplicate keys and confirm that the first match is the record you intend to return.

A quick equality check can help identify whether two references compare as equal, although it does not explain why they differ. With the candidate source key in A2 and the lookup value in E2, enter:

=A2=E2

TRUE means Excel considers the two values equal for that comparison; FALSE means they are not equivalent as stored. It is a diagnostic, not a replacement for checking type, spacing, and the selected lookup range.

When XLOOKUP or INDEX and MATCH Is Better

VLOOKUP remains useful for straightforward tables, but it has a fixed left-to-right design: the lookup column must be first, and the return column is specified by a numeric position. Insertions or rearrangements can make that position harder to maintain. XLOOKUP can search one range and return a corresponding value from another, including a column to the left, and its exact match behavior is the default. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel releases.

For example, with keys in C2:C100, results in A2:A100, and the search value in E2, this XLOOKUP formula searches C and returns from A:

=XLOOKUP(E2,C2:C100,A2:A100,"Not found")

INDEX and MATCH is another option, including in older Excel versions. It separates the return range from the lookup range and can search in either direction. These alternatives do not automatically fix dirty or inconsistent data: text-versus-number differences and hidden characters can affect them too. Standardizing the data is still the durable solution.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the values look identical?
They may differ in stored type or contain spaces or invisible characters. They may also be displayed identically while one is a real number or date and the other is text. Check the fourth argument, the source key, and the underlying cell contents.

Does VLOOKUP need the lookup column to be sorted?
Not when you specify FALSE for an exact match. Sorting is required for reliable approximate matching with TRUE or an omitted final argument. For ordinary lookup lists, using FALSE avoids the sorted-range requirement.

Can VLOOKUP distinguish uppercase from lowercase?
No. VLOOKUP’s text matching is not case-sensitive, so ABC and abc are treated as matching text. If case must matter, use a case-sensitive approach such as a formula built with EXACT rather than relying on VLOOKUP alone.

Why does VLOOKUP return the wrong matching record?
The formula may be using approximate matching, or the key may appear more than once. With exact matching and duplicates, VLOOKUP returns the first match from the top of the lookup range. Check the match argument and whether the key should be unique.

Will changing the cell format fix a text-number mismatch?
Usually not. Formatting changes how a value appears, but it does not necessarily change the stored value’s type. Convert or clean the values consistently in both the lookup value and source key column, while preserving leading zeros when they are meaningful.

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.