Why Is My VLOOKUP Not Working? Common Fixes

VLOOKUP usually fails because its last argument is missing or set to approximate match, the lookup value does not exactly match the source data, or the table range and column number are wrong. In Excel for Microsoft 365 on Windows, start by adding FALSE as the fourth argument for an exact match, then check the lookup column, data types, spaces, and copied cell references.

How VLOOKUP works

VLOOKUP searches vertically down the first column of a range for a value, then returns a value from another column on the same row. Its arguments are the value to find, the table range, the number of the return column within that range, and an optional match setting. That last setting is a frequent source of surprising results.

For example, suppose IDs are in A2:A100, product names are in B2:B100, and F2 contains the ID to find. If A2 contains 1042 and B2 contains Widget, this formula returns Widget when F2 contains 1042:

With IDs in A2:A100, product names in B2:B100, and the ID to look up in F2, use:

=VLOOKUP(F2,A2:B100,2,FALSE)

The range A2:B100 is the table array. VLOOKUP searches only its leftmost column, A, and the number 2 means return the second column of that selected range, B. It does not mean worksheet column B in every formula: if the selected range starts in C, then 1 refers to C and 2 refers to D.

Check the match setting first

For most lists of names, product codes, invoice numbers, or IDs, you want an exact match. Use FALSE as the fourth argument. TRUE requests an approximate match, and if you omit the fourth argument Excel treats it as TRUE. Approximate matching is useful for sorted thresholds, such as tax bands or grading cutoffs, but is often wrong for a lookup table where each key should match precisely.

With approximate matching, the first column of the range must be sorted in ascending order for dependable results. If the lookup value falls between two entries, VLOOKUP can return the row for the closest smaller value. If the data is unsorted, the result may be incorrect or an error. This can look like a successful lookup even though the returned answer is wrong, so check the fourth argument whenever a result seems plausible but unexpected.

With exact matching, FALSE tells Excel to look for an equal value. If there are duplicate matching values in the first column, VLOOKUP returns the value from the first matching row it encounters. It does not report that duplicates exist or automatically choose the newest or last record.

Diagnose the error or wrong result

The specific symptom narrows down the cause. A formula can display an error, return a blank-looking value, or return a valid-looking answer from the wrong row. Check the formula and the data before wrapping it in error-handling functions: hiding an error does not correct the underlying lookup.

#N/A: With FALSE, Excel did not find an exact match. Check spelling, spaces, data types, and whether the lookup value is actually in the first column of the selected range. With approximate matching, the range may be unsorted or the lookup value may be below the smallest entry.
#REF!: The column number asks for a column beyond the right edge of the table range. For instance, a range with two columns cannot return column 3.
#VALUE!: The column number is less than 1, or an argument is otherwise invalid. Confirm that the third argument is a positive column number within the selected range.
A wrong value: The fourth argument may be omitted or TRUE, the return-column number may point to the wrong field, or a duplicate key may appear earlier in the table.
A blank-looking result: The matching source cell may be empty, or it may contain a formula returning an empty string. The lookup may have succeeded even though the displayed result looks blank.

Read the formula from left to right and verify each part: the lookup cell, the table boundaries, the return-column number, and the match setting. In Excel, select the formula cell and inspect the formula bar; clicking the range reference in the formula can also make its selected cells easier to identify. If the formula was copied from another row, check whether its references shifted.

Make sure the lookup column is first

VLOOKUP cannot search one column and return a value from a column to its left. The lookup values must be in the leftmost column of the table array, and the return column must be to their right. If your IDs are in column D but the value you need is in column B, selecting B:D will not solve the problem: VLOOKUP will search B, not D.

You can often fix this by selecting a range that starts with the lookup column and extends to the return column. If the return data is genuinely to the left, rearrange or copy the source data if that is appropriate, or use a function that can look in either direction. In Microsoft 365, XLOOKUP is a common alternative and does not require the lookup column to be at the left edge of the selected data. VLOOKUP is still useful when working with older workbooks or when its left-to-right layout fits the data.

Check spaces, spelling, and data types

Values that look identical on screen may not be equal to Excel. A code stored as text, such as text “1042”, is different from the number 1042 in some lookup situations. Imported files can also contain leading or trailing spaces, nonbreaking spaces, or other invisible characters. Dates can have a similar problem: a displayed date may be text in one list and a real Excel date serial number in another.

Compare the source and lookup values in the formula bar, and check how the cells are formatted and stored. Formatting a text value to look like a number does not necessarily convert it into a numeric value. Likewise, changing a number format changes its display, not the underlying value. Convert or clean the data consistently in both the lookup value and the source column rather than modifying only one side.

For ordinary leading and trailing spaces, TRIM can help. CLEAN removes some nonprinting characters, and SUBSTITUTE with CHAR(160) can replace the nonbreaking space commonly found in copied web data. With the imported text to clean in A2, this formula returns a cleaned version in the cell where you enter it:

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

Use the same cleaning approach on the lookup keys and the source keys, then make the lookup against those cleaned values. These functions do not fix every possible character or data-quality issue. TRIM, for example, does not remove every Unicode spacing character, so if two values still fail to match, investigate the actual imported text rather than assuming the displayed strings tell the whole story.

Exact text matches are not case-sensitive: VLOOKUP generally treats uppercase and lowercase versions of the same text as equal. If case itself must distinguish records, VLOOKUP is not a suitable case-sensitive lookup by itself. Also remember that an asterisk (*) and question mark (?) can act as wildcards in text searches; a tilde (~) is used to search for a literal wildcard character.

Verify the table range and column number

A lookup can fail after rows are added if the formula’s range does not include them. Check that the table array covers every relevant data row and that it begins with the lookup column. When a formula is filled down, relative references move: a range such as A2:B100 can become A3:B101. That shift may be intentional in some formulas, but for a fixed lookup table it can gradually exclude the first rows and include empty ones.

With lookup values in F2:F20, IDs in A2:A100, and return values in B2:B100, use dollar signs to keep the table range fixed as you copy the formula down:

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

Here F2 is deliberately relative so it can become F3, F4, and so on. The dollar signs lock the source range. You can also use an Excel table as the source so its references can expand when rows are added, but confirm that the table’s first column is still the lookup column and that the formula refers to the intended columns.

Then count the columns from the left edge of the selected range to the return field. If the range is B:D, the return column in D is 3, not 4. Inserting or rearranging columns can also make a hard-coded column number point to a different field than before. A formula that still calculates is not necessarily returning the intended column, so verify the header and a known example.

Use a more flexible alternative when appropriate

If a lookup needs to search to the left, or if a fixed column number is making a workbook fragile, XLOOKUP may be a better fit in Microsoft 365 and Excel 2021 or later. It lets you specify a lookup range and a separate return range, and it can provide a chosen value when no match is found. This can make the formula easier to understand when columns are inserted or moved. However, people using older Excel versions may not be able to open or calculate a workbook that relies on newer functions, so consider who needs to use the file.

INDEX and MATCH are another established option for flexible lookups, including searches where the return column is to the left. VLOOKUP remains straightforward for tables arranged with keys first, especially when a workbook needs compatibility with a broad range of Excel versions. Choose based on the workbook’s layout and the people who need to maintain it, rather than replacing a working formula just because another function exists.

A reliable troubleshooting sequence

When the cause is not obvious, make one check at a time and test the formula with a value you can verify manually. This avoids changing several things at once and losing track of which change fixed the issue.

Confirm that the lookup value really appears in the first column of the selected range.
Set the fourth argument to FALSE unless you specifically need an approximate threshold lookup.
Check that the range includes the matching row and the desired return column.
Count the return column from the left edge of the table array, starting at 1.
Compare the source and lookup values for text-versus-number differences, spaces, hidden characters, and inconsistent dates.
If copying the formula, lock the source range with dollar signs and let only the lookup-cell reference change.
Check for duplicate keys and verify the result against the corresponding row in the source data.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
It may only appear to be the same value. Check whether one entry is text and the other is numeric, look for extra or nonbreaking spaces, and confirm that the match is in the first column of the table array. For an exact lookup, use FALSE as the fourth argument.

Why does VLOOKUP return the wrong row?
The formula may be using approximate match because its fourth argument is TRUE or omitted. Use FALSE for an exact match. If the match is exact, check the return-column number and whether an earlier duplicate key is being returned.

Does VLOOKUP search from right to left?
No. It searches the first column of its table array and returns data from a column to the right. Use XLOOKUP in supported Excel versions, or another lookup method such as INDEX and MATCH, when the return column is to the left.

How do I keep the VLOOKUP range from moving?
Add dollar signs to the source range, as in $A$2:$B$100, while leaving the lookup-cell reference relative if it should change when copied down. This keeps the table fixed without making every formula search the same lookup value.

Is VLOOKUP case-sensitive?
No. Standard VLOOKUP matching does not distinguish uppercase from lowercase letters. If capitalization must be treated as part of the key, use a method designed for case-sensitive comparison 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.