VLOOKUP Formula Correct but Not Working? Common Fixes

If your VLOOKUP formula looks correct but is not working, first make it an exact match by setting its fourth argument to FALSE, then check that the lookup value is in the first column of the selected range and has the same underlying value type as the data. A wrong range, an incorrect return-column number, or hidden spaces can also cause an error or an unexpected result.

How VLOOKUP works

VLOOKUP searches down the first column of a range for a value, then returns a value from another column on the same row. The return column is counted from the left edge of the selected range, not from column A on the worksheet. That distinction matters when you select a range that starts partway across the sheet.

For example, suppose E2 contains a product code, A2:A100 contains product codes, and C2:C100 contains prices. With that layout, this formula searches for the code in E2 and returns the matching price from the third column of A:C:

=VLOOKUP(E2,A2:C100,3,FALSE)

If the matching code is on row 7 and C7 contains 12.50, the result is 12.50. The final argument, FALSE, asks Excel for an exact match. It is usually the right choice for codes, names, invoice numbers, and other identifiers. You can use 0 as an equivalent exact-match argument.

VLOOKUP cannot search to the left: the lookup column must be the first column in the selected range. It also returns the first matching row when the lookup column contains duplicates. Those are limitations of the function, not necessarily evidence that the formula is mistyped.

Start with the exact-match setting

If you omit VLOOKUP’s fourth argument, Excel uses approximate matching. That behavior can produce a plausible but wrong result instead of an obvious error. Approximate matching is intended for sorted data and threshold tables, such as tax bands or grading cutoffs; the first column must be arranged in ascending order. On unsorted data, the returned result may be unreliable.

For a typical lookup where the value should match a row exactly, set the final argument to FALSE. With product codes in A2:A100, descriptions in B, prices in C, and the requested code in E2, the formula below returns the price column for an exact match:

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

The dollar signs keep the lookup range fixed if you copy the formula down a column. Without them, Excel may shift the range as the formula is filled down, causing later rows to search a different set of cells. If one row works and the next does not, select the later formula and compare its range with the working one.

Check the lookup column and return-column number

Count the return column from the left edge of the table array. If your selected range is B2:D100, then B is column 1, C is column 2, and D is column 3—even though D is the fourth worksheet column. A column number that is larger than the number of columns in the selected range commonly causes a #REF! error. A column number below 1 can cause #VALUE!.

Also confirm that the lookup field really is the first column of the range. If employee names are in B and you select A:C, VLOOKUP searches A, not B. Rearrange the selected range so the lookup field is first, or use another lookup method when changing the layout is impractical. Check that the return-column number points to the field you want; a formula can calculate successfully while returning a neighboring column by mistake.

Look for numbers stored as text

Excel can display a numeric-looking value while storing it as text. A cell containing the text 0042 is not always equivalent to a cell containing the number 42: the leading zeros may be meaningful in a product or account code. Exact-match lookups can fail when the lookup value and lookup column use different types, or when one version contains leading zeros that the other does not.

Compare a sample value on each side of the lookup. Check the formula bar, test whether the cells are left- or right-aligned by default, and inspect the number format. Alignment is only a clue, not proof, because formatting and manual alignment can change how cells appear. Decide whether the field is an identifier, where text may be appropriate, or a quantity, where numeric values are usually more suitable. Convert the source data consistently rather than changing only the lookup cell without checking the consequences.

For a specific example, suppose E2 contains the text 42 and A2:A100 contains numeric IDs, while the prices are in C. If the text contains a valid number, VALUE can convert E2 for this lookup:

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

If the matching numeric ID is 42 and its price is 12.50, the result is 12.50. This approach is unsuitable when leading zeros distinguish codes: converting 0042 to a number would discard that distinction. In that case, make both lookup values and source codes consistent text instead.

Remove extra spaces and invisible characters

A value can look identical on screen but contain a leading space, a trailing space, or a nonprinting character. These commonly arrive in data copied from websites, exported reports, or other systems. A regular space before a code is enough to prevent an exact text match. Inspect suspicious cells in the formula bar, or compare their lengths if the visible text appears identical.

For ordinary extra spaces, TRIM can clean the lookup value. With the lookup text in E2, keys in A2:A100, and the desired result in C, this formula trims spaces from E2 before searching:

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

If E2 contains a leading or trailing ordinary space and the matching key is otherwise present, the formula returns the value from column C on that row. This does not clean the source column, so it will not solve every inconsistency. TRIM also does not remove every character that resembles a space; nonbreaking spaces, often represented by character code 160, may need to be replaced or cleaned in the source data. CLEAN removes certain nonprinting characters, but it is not a universal Unicode cleanup tool.

When the problem affects many rows, it is usually better to create a cleaned helper column for the lookup keys and use it consistently on both sides. That makes the transformation visible and repeatable, rather than hiding a data-quality issue inside one lookup formula.

Interpret the error before changing the formula

The error message narrows down the cause. #N/A generally means Excel did not find a match under the selected matching rules. Check exact versus approximate matching, data types, spaces, spelling, and whether the value actually exists in the lookup column. #REF! often points to an invalid return-column number or a reference that was deleted. #VALUE! can indicate an invalid column number or another argument problem.

A wrong answer with no error often points to approximate matching, duplicates, a shifted range, or an incorrect return-column number. If duplicate keys exist, VLOOKUP returns the first matching row from top to bottom; it does not warn that later rows have the same key. Sort or review the source records if the first occurrence is not the desired one.

Be cautious about wrapping a lookup in IFERROR. It can replace a visible error with a message, but it does not repair the lookup. With E2 as the requested code, keys in A2:A100, and return values in C, this formula displays a message whenever the inner lookup produces an error:

=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")

If the value is genuinely absent, the result is Not found. But the same message can hide a broken range or another error, so first diagnose the underlying issue. If you want to distinguish an absent key from other formula problems, avoid masking every error with the same text.

Check range references, recalculation, and display

When filling a formula down, confirm that the lookup table stays in place. Absolute references, shown with dollar signs, prevent the range from moving. If the source data grows beyond the selected range, new records will not be found until the range includes them. An Excel Table can make source ranges easier to maintain as rows are added, though the formula still needs the correct lookup column and return field.

If source values or formulas have changed but the displayed result has not updated, check calculation settings. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. When calculation is set to Manual, press F9 to calculate. On a Mac, check the Calculation Options menu on the Formulas tab; menu presentation and keyboard shortcuts can differ from Windows. Recalculation will not fix a bad match, but it can resolve a stale result.

Formatting is another frequent source of confusion. Number formatting can make 42 appear as 0042, or show a date in a different style, without changing the stored value. VLOOKUP returns the underlying value from the return cell; the displayed appearance may depend on the destination cell’s format. Formatting alone does not make two underlying text values match, nor does it convert a text code into a number.

Use a reliable troubleshooting sequence

Rather than rewriting the formula at random, check one cause at a time. This makes it easier to tell whether the repair worked and prevents a workaround from concealing a separate problem.

Confirm the lookup value exists in the first column of the selected range.
Set the final argument to FALSE when the lookup needs an exact match.
Verify that the return-column number is counted from the left edge of the range.
Compare the underlying types and contents of the lookup value and source key, including leading zeros and spaces.
Check for duplicate keys, a range that shifts when copied, or new data outside the selected range.
Read the actual error before using IFERROR, and check calculation settings if the result appears stale.

Excel’s Formulas > Evaluate Formula command can step through a formula to show how Excel evaluates it. This is useful when the reference or intermediate lookup value is not obvious. You can also temporarily select the lookup cell and source key to inspect their contents directly; avoid relying only on how they look when formatted.

When to use an alternative

VLOOKUP remains useful for straightforward tables, especially when other people need to understand a familiar formula. Consider XLOOKUP when you need to search in either direction, specify a result for a missing key directly, or avoid counting a return-column index. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel versions. It will not automatically correct inconsistent data or decide which duplicate record is appropriate.

INDEX and MATCH are another option in older Excel versions, particularly when the return column is to the left of the lookup column. They can make a formula more flexible, but they do not eliminate the need to check data types, exact matches, duplicates, and source quality. Changing functions is not a substitute for identifying why the current lookup fails.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is clearly there?
Check whether the lookup value and source key are truly the same: one may be text and the other numeric, or one may contain a space or hidden character. Confirm the lookup column is first in the selected range and use FALSE for an exact match.

Why does VLOOKUP return the wrong row instead of an error?
The formula may be using approximate matching because its final argument is TRUE or omitted. Use FALSE for an exact lookup. If the key appears more than once, VLOOKUP returns the first matching row, so also inspect duplicates.

Why does VLOOKUP work in one row but not after I copy it?
The lookup range may have moved as the formula was filled down. Lock a fixed range with dollar signs, then check that each row still uses the intended lookup cell and return-column number.

Does changing the cell format fix a text-versus-number mismatch?
Usually not. Number formatting changes how a value is displayed, not necessarily the value stored in the cell. Convert the lookup value and source keys to a consistent type, while preserving leading zeros if they are part of an identifier.

Why does VLOOKUP find the wrong duplicate?
VLOOKUP returns the first matching key it encounters from the top of the lookup column. Remove or resolve duplicates, arrange the records so the intended match comes first, or use a method that explicitly selects the desired record.

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.