VLOOKUP Not Working? Common Causes and Fixes

When VLOOKUP does not work, it is usually because the lookup value is not an exact match, the formula is searching the wrong column, the return-column number is wrong, or Excel is using approximate matching. Check the formula’s final argument, the leftmost column of its lookup range, and whether the values truly share the same type and characters; those checks resolve most errors and incorrect results.

How VLOOKUP works—and where it is limited

VLOOKUP searches for a value in the first, or leftmost, column of a rectangular range, then returns a value from another column on the same row. Its arguments specify the value to find, the table range, the position of the result column within that range, and whether to use an approximate or exact match. The result-column position is counted from the left edge of the selected range, not from the worksheet’s column A.

For example, if a range starts in column C and the result is in column E, the return-column number is 3: C is 1, D is 2, and E is 3. VLOOKUP cannot search a column to the right and return a value from a column to its left. That structural limitation is a frequent reason a formula seems impossible to write, rather than a calculation error.

In Microsoft Excel for Microsoft 365, the safest default for ordinary lookups is an exact match. The final argument FALSE requests an exact match; omitting that argument does not. When it is omitted, VLOOKUP uses approximate matching, which can return a plausible but incorrect answer.

Start with an exact-match formula

With product codes in A2:A5, prices in B2:B5, and the code to find in E2, enter this formula in F2:

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

If A2:A5 contains B201, B204, B208, and B210, and B204 is listed with a price of 18.50 in B3, the formula returns 18.50 when E2 contains B204. FALSE tells Excel to look for that exact code. The dollar signs keep the table range fixed if you copy the formula down; they do not change which value E2 refers to.

If the formula is copied down a list of lookup values, check that the lookup cell reference changes by row while the table range stays fixed. A moving range can eventually exclude the matching row. Conversely, if every copied formula keeps looking up E2, the lookup-value reference may have been made absolute when it should change.

Understand the error you see

The displayed error often narrows the diagnosis. A wrong answer with no error can be harder to spot, so check both the result and the formula’s logic.

#N/A: Excel did not find a match under the formula’s matching rules. Check for a missing key, extra spaces, different data types, or a wrong lookup range.
#REF!: The return-column number points beyond the columns in the selected table range. Expand the range or correct the number.
#VALUE!: The return-column number is less than 1, or another argument has an invalid value. Check that the third argument is a positive column position.
#NAME?: Excel may not recognize the function name or text in the formula. Check spelling and make sure text values are enclosed in straight double quotation marks when needed.

A #N/A result is not automatically proof that the key is absent from the worksheet. It may be present but stored differently, contain an invisible character, or fall outside the selected range. Also, replacing every error with a blank can hide a real problem. During troubleshooting, leave the original error visible until you know why it occurs.

Check the lookup column and return-column number

The first column of the table range must contain the lookup values. Suppose product codes are in B2:B100 and descriptions are in A2:A100. A range beginning at A2 puts descriptions first, so VLOOKUP cannot use that range to search for a code in B. Select a range beginning in B, or use another lookup method that can search in either direction.

Next, count the result column from the left edge of the range. If the range is B2:D100, then B is column 1, C is 2, and D is 3. A common mistake is to enter the worksheet column number—for example, 4 for column D—instead of the position within the selected range. If the formula returns data from the wrong field but no error, this count is worth checking.

Keep the range wide enough to include both the lookup column and the desired result column, but ensure it starts at the lookup column. When a column is inserted or removed, a hard-coded return-column number may no longer identify the intended field. Review the formula after structural changes rather than assuming its old number still points to the correct data.

Look for text, number, and spacing mismatches

Two values can look identical on screen but differ to Excel. A number such as 204 may be stored as a number in one cell and as text in another. Codes with leading zeros are especially likely to be text: 0042 is not safely interchangeable with the number 42 if those zeros are part of the identifier. Choose a consistent representation for the source and lookup values rather than converting codes blindly.

Leading or trailing spaces can also prevent a match. Imported data may contain nonprinting characters or nonbreaking spaces that ordinary cleanup does not remove. To investigate, compare the cells in the formula bar, inspect their number formats, and test cleaned copies in spare columns. TRIM can remove ordinary extra spaces from text, but it does not remove every nonprinting character; CLEAN can remove certain nonprinting characters, but not all unusual imported whitespace.

For a small table, retyping a suspicious key may reveal a hidden-character issue. For a larger import, clean the source data consistently and then look up against the cleaned column. Avoid changing the display format alone: applying a number format changes how a value appears, not necessarily whether the underlying value is text or numeric.

Know when approximate matching is appropriate

TRUE, or an omitted final argument, asks VLOOKUP for an approximate match. In that mode, the first column should be sorted in ascending order. Excel finds an exact value if available; otherwise it returns the row for the largest first-column value that is less than or equal to the lookup value. If the first column is not sorted as required, the result can be wrong without an error.

Approximate matching is useful for threshold tables, such as assigning a rating based on a minimum score. With minimum scores 0, 60, 70, and 85 in A2:A5, their labels in B2:B5, and a score of 74 in E2, use:

=VLOOKUP(E2,$A$2:$B$5,2,TRUE)

If the labels are Fail, Pass, Good, and Excellent in that order, the result is Good: 70 is the greatest threshold no higher than 74. This works because the thresholds are ascending. For product IDs, names, invoice numbers, or other keys where only identical values should match, use FALSE instead.

Check duplicates, wildcards, and silent wrong answers

When the lookup column contains duplicate values, VLOOKUP returns the first matching row it encounters from top to bottom. It does not warn that later rows share the same key. If duplicate keys are legitimate, decide which record should take priority and arrange or redesign the data accordingly; if they are accidental, remove or resolve them at the source.

Exact matching is not case-sensitive: codes ABC and abc are treated as equal by VLOOKUP. In addition, text lookups can interpret an asterisk (*) as any sequence of characters and a question mark (?) as any single character. To search for one of those characters literally, precede it with a tilde (~). These wildcard rules can produce unexpected matches when punctuation is part of a code.

Wrong results can also come from selecting the wrong table or returning the wrong field. Before trusting a result, choose a key whose correct answer you already know and trace the matching row by hand. This simple spot-check is useful after changing a range, sorting a table, or filling a formula down many rows.

Use error handling and consider alternatives

After the lookup is working, you can display a helpful message when an exact match is absent. With the same product table in A2:B5 and lookup code in E2, use:

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

If E2 contains a code not present in A2:A5, the result is Not found. IFNA catches #N/A specifically, while allowing other errors—such as #REF! from an invalid column number—to remain visible. That distinction helps avoid disguising a broken formula as an ordinary missing record.

In Microsoft 365, XLOOKUP is an alternative that can search one range and return a corresponding value from another, including a column to the left. With product codes in A2:A5, prices in B2:B5, and the code in E2, this formula returns the matching price or the message shown:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. VLOOKUP remains useful in older workbooks and when a familiar table layout makes its left-to-right search suitable. Changing functions will not fix dirty or inconsistent source data, so confirm the keys and intended match behavior whichever function you choose.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
Often the values differ in type or hidden characters, or the lookup range excludes the row. Check text versus numbers, spaces, and the range’s first column. Use FALSE for an exact lookup unless approximate matching is intentional.

Why does VLOOKUP return the wrong value instead of an error?
The formula may be using approximate matching, pointing to the wrong return column, or finding the first of several duplicate keys. Check the final argument, count columns from the range’s left edge, and inspect duplicates.

Does VLOOKUP need the first column to be sorted?
For exact matching with FALSE, sorting is not required. For approximate matching with TRUE or an omitted final argument, the first column must be sorted in ascending order for reliable results.

Can VLOOKUP search left?
No. It searches the leftmost column of its table range and returns a value from a column to its right. Use XLOOKUP in supported Excel versions, or another method such as INDEX and MATCH, when the result is to the left.

Is VLOOKUP case-sensitive?
No. VLOOKUP treats uppercase and lowercase letters as equivalent. If a lookup must distinguish case, use a different approach designed for case-sensitive comparisons.

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.