Why Is VLOOKUP Not Working? Common Excel Problems & Fixes

VLOOKUP usually fails because its match setting is wrong, the lookup value does not exactly match the first column of the search range, or the formula points to the wrong column or range. Check the fourth argument first: use FALSE for an exact match, then verify the data and references if the result is still wrong.

How VLOOKUP works

VLOOKUP searches down the first column of a rectangular range for a value, then returns a value from another column in the same row. Its arguments are the value to find, the range to search, the number of the return column within that range, and an optional match setting. The function cannot look to the left: the lookup column must be the leftmost column in the selected range.

For example, with product IDs in A2:A100, descriptions in B2:B100, prices in C2:C100, and the requested ID in E2, the formula below searches column A and returns the price from the third column of the selected range. If E2 contains P-104 and that row’s price is 18.50, the result is 18.50.

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

The number 3 counts columns from the left edge of A2:C100, not from the worksheet’s column A. That distinction matters when the selected range starts somewhere other than column A. A correct-looking formula can return the wrong field if this index is miscounted.

Exact match versus approximate match

A frequent cause of unexpected results is omitting the fourth argument. When it is omitted, VLOOKUP uses approximate matching. Approximate matching is intended for sorted lookup values, such as tax bands or grading thresholds. It finds an exact match when available; otherwise it can return the result associated with the nearest lower value. If the lookup column is not sorted in ascending order, it may return an incorrect result.

For identifiers such as employee numbers, invoice codes, product IDs, or names, exact matching is usually the right choice. Set the final argument to FALSE (or 0). If an exact match is absent, Excel returns #N/A rather than quietly choosing a nearby value. This is often useful: it makes a missing record visible instead of presenting a plausible but incorrect answer.

Approximate matching is not inherently broken, but it is easy to use accidentally. If a formula returns a value even though the lookup entry is not present, inspect whether its final argument is TRUE or left blank. Also confirm that approximate-match data is sorted correctly and that its breakpoints represent the result you intend.

What common VLOOKUP errors mean

The displayed error often narrows down the cause. Before changing the formula, note whether Excel shows an error, a blank, or a valid-looking value that is simply wrong.

#N/A: VLOOKUP did not find a match under the selected match rules. Check spelling, spaces, data types, the first column of the range, and whether exact matching is enabled.
#REF!: The requested return-column number is greater than the number of columns in the selected range, or part of the referenced range was deleted. Recount the columns and repair the range.
#VALUE!: The return-column number is less than 1, or a formula argument has an invalid value. Check that the third argument is a positive column number.
#NAME?: Excel may not recognize the function name or a text value in the formula. Check for a typo, missing quotation marks around literal text, or a function name entered incorrectly.
Wrong result with no error: Approximate matching, a duplicate key, a wrong column index, or an incorrectly selected range can all produce a valid-looking but unintended answer.

For #N/A, make sure the value you want to search for is actually in the range’s first column. Selecting B2:C100 when the IDs are in column A cannot work as intended, even if the desired return value is visible nearby. VLOOKUP does not search every column in its table array.

Numbers, text, spaces, and hidden characters

Two values can look identical on screen but be different to Excel. A cell containing the number 104 is not necessarily the same as a cell containing the text 104. This commonly happens when data is imported from a CSV file, copied from a web page, or assembled from different systems. A numeric ID on one sheet and a text ID on another can therefore produce #N/A.

Check the underlying values rather than relying only on their appearance. Number formats affect display, but applying a number format does not always convert text into a number. Depending on the data, you can convert the source values consistently, use Text to Columns, or create a cleaned helper column. Avoid converting identifiers to numbers if leading zeros are meaningful: the text code 00104 is not interchangeable with the number 104.

Spaces are another frequent culprit. A trailing space after a code, a leading space before a name, or a nonbreaking space copied from a website can prevent an exact match. With the dirty value in A2, this helper formula removes ordinary extra spaces and many nonprinting characters, and replaces nonbreaking spaces before trimming; for a cell containing spaced text P-104, it returns P-104.

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

Apply consistent cleanup to both the lookup value and the lookup column; cleaning only one side will not make different strings equal. TRIM alone does not remove every invisible character, which is why imported data may need further inspection. CLEAN and SUBSTITUTE are useful tools, but they cannot resolve every possible encoding or data-quality issue automatically.

Check the range and return-column number

VLOOKUP’s table range must include both the lookup column and the return column. If the lookup key is in A and the return field is in C, a range beginning at B excludes the key; a range ending at B excludes the result. If the formula is copied down, a relative range may also shift row by row. In the example formula, dollar signs in $A$2:$C$100 keep the table range fixed as the formula is filled down.

The third argument is a position inside the selected table, not a worksheet column letter. For a range D2:F50, D is position 1, E is position 2, and F is position 3. If you insert or rearrange columns in a workbook, review the index because VLOOKUP does not automatically know which field you meant to return. A formula can remain syntactically valid while pointing at a different field after a layout change.

On Windows, select a cell reference in the formula and press F4 to cycle through absolute and relative reference styles. On a Mac, the equivalent key can depend on the keyboard and function-key settings; use the Fn key with F4 if the function keys control hardware features, or type the dollar signs directly. Confirm the resulting reference before copying the formula.

Duplicates, wildcards, and case sensitivity

When the first column contains duplicate lookup values, VLOOKUP returns the first matching row it encounters from the top. It does not report that duplicates exist or automatically select the most recent record. If the answer seems inconsistent, sort order or duplicate keys may explain it. Decide which record should win, then make the key unique or use a method that selects the intended row.

Exact matching is not case-sensitive: codes ABC and abc are treated as equivalent for a normal VLOOKUP. If capitalization distinguishes records, VLOOKUP by itself is not enough to enforce that distinction. Also, in exact-match mode, the characters * and ? can act as wildcards; a tilde can be used to escape a wildcard character when you need to search for it literally. This is a less common source of surprising matches in keys that contain those symbols.

Formula entry and calculation settings

Confirm that the cell contains a formula, not text that merely looks like one. A leading apostrophe or a cell formatted as Text before the formula was entered can cause Excel to display the formula instead of calculating it. Change the cell to General, then re-enter the formula. Also check for a missing equals sign, an unmatched parenthesis, or a separator that does not match the computer’s regional settings. Some Excel installations expect semicolons between arguments instead of commas; use the separator Excel accepts in that installation.

If a result appears stale after the source data changes, check the calculation mode in Excel for Microsoft 365 on Windows: go to Formulas > Calculation Options and select Automatic. Manual calculation can leave formulas waiting to update. Excel for Mac also provides calculation options on the Formulas tab, though the precise interface can differ by version. If the calculation mode is already Automatic, inspect whether the referenced cells themselves contain the latest values.

A practical troubleshooting sequence

Work from the simplest checks to the more subtle ones. This keeps a small formula problem from turning into unnecessary data cleanup.

Look at the result type: is it #N/A, another error, a displayed formula, or a plausible but incorrect value?
For ordinary IDs and labels, set the fourth argument to FALSE or 0.
Confirm that the lookup value exists in the leftmost column of the table array.
Compare both values for text-versus-number differences, leading zeros, extra spaces, and hidden characters.
Recount the table columns and check that the return-column number points to the intended field.
Lock the table range if the formula will be copied, then check duplicates and calculation settings.

Test the formula against one row you can verify manually. For instance, find the exact key in the source table, confirm the return value in that row, and compare it with the formula’s output. If the manual row and formula disagree, the cause is likely in the match mode, range, index, or data consistency—not in the arithmetic of the returned value.

When to use an alternative

VLOOKUP remains useful in older workbooks and straightforward tables, but its leftmost-column requirement and numeric column index can make formulas fragile. In Microsoft 365, XLOOKUP can search one range and return from another, including a column to the left, and its arguments make exact matching the normal behavior. XLOOKUP is also available in Excel 2021 and Excel 2024, but not in some older Excel releases.

With the key in E2, lookup IDs in A2:A100, and prices in C2:C100, this XLOOKUP formula returns the matching price or the text Not found if there is no match. If E2 is P-104 and its price is 18.50, the result is 18.50.

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

For workbooks that must support older Excel versions, INDEX with MATCH is another established option. Changing functions will not fix dirty or inconsistent data by itself, however. If VLOOKUP’s limitation is simply a reversed table layout, reorganizing the data or using a suitable alternative is more reliable than repeatedly adjusting a column index.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
It may not be an exact match internally. Check whether one value is text and the other a number, whether either has extra or invisible spaces, and whether the value is in the first column of the selected range. Use FALSE for an exact match.

Why does VLOOKUP return the wrong value instead of an error?
The fourth argument may be TRUE or omitted, allowing approximate matching, especially problematic with unsorted data. Other possibilities include a duplicate key, a wrong column index, or a range that points to an unintended table. Verify the returned row and column against the source.

Can VLOOKUP search to the left?
No. It searches the first column in its table array and returns a value from a column to the right. Use XLOOKUP in a supported version of Excel, or use INDEX and MATCH, when the result column is left of the lookup column.

Why does my VLOOKUP formula show instead of calculate?
The cell may be formatted as Text, the formula may begin with an apostrophe, or Excel may be displaying formulas. Change the cell format to General and re-enter the formula; also check Excel’s calculation settings if results are not updating.

Does VLOOKUP distinguish uppercase and lowercase?
No. Standard VLOOKUP matching is not case-sensitive, so ABC and abc match as equivalent text. If case is a meaningful part of the key, use a case-sensitive approach rather than relying on VLOOKUP alone.

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.