VLOOKUP Not Working? Causes and Fixes

VLOOKUP usually stops working because it cannot find an exact match, the lookup column is not the first column in the selected range, or the formula points to the wrong cells. Check the error shown, confirm that the lookup value and source data have the same type and spelling, and use an exact-match setting when you need an exact result.

How VLOOKUP searches for a result

VLOOKUP searches down the leftmost 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 range to search, the number of the return column within that range, and an optional match setting. That last setting is a frequent source of unexpected results.

For an exact lookup, the fourth argument should be FALSE. With employee IDs in A2:A100, corresponding names in B2:B100, and the ID to find in D2, use:

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

This searches column A and returns the matching row’s value from the second column of the selected range, column B. The dollar signs keep the search range fixed if you copy the formula down. In Microsoft 365 for Windows, press F4 while editing a cell reference to cycle through reference styles; the corresponding Excel for Mac shortcut is usually Command+T.

If you omit the fourth argument, Excel uses approximate matching. That can return a plausible but incorrect result when you expected an exact one. Approximate matching is useful for banded values such as tax brackets or score ranges, but the first column must be sorted in ascending order for the result to be reliable. For IDs, names, order numbers, and other discrete keys, use FALSE.

Check that the lookup column is first

VLOOKUP cannot search one column and return a value to its left. The column containing the lookup value must be the leftmost column in the table range. For example, if IDs are in column C and names are in column B, selecting B:C will not let VLOOKUP search C and return B: it searches B first.

There are a few ways to resolve that limitation:

Rearrange or copy the data so the lookup column is at the left of the range.
Select a range that begins with the lookup column and extends right to the return column, if the desired result is to its right.
Use XLOOKUP, available in Microsoft 365 and Excel 2021 or later, which can look in one range and return from another range on either side.

For example, with IDs in C2:C100, names in B2:B100, and the ID to find in E2, an XLOOKUP formula can return the name to the left:

=XLOOKUP(E2,C2:C100,B2:B100,"Not found",0)

The final 0 requests an exact match, and the text provides a readable result when no match exists. In Excel 2019 and earlier, XLOOKUP is not available; INDEX and MATCH are a common alternative.

Understand the error message

The error displayed in the result cell often narrows the search. It does not always identify the underlying cause, so check the formula and source data as well.

#N/A: Excel did not find a suitable match. Common causes include a missing value, different data types, extra spaces, a misspelling, or a lookup range that starts in the wrong column. Approximate matching against unsorted data can also give unexpected results.
#REF!: The column number asks for a column beyond the width of the selected range, or a referenced cell or range was deleted. If the table range has two columns, a column number of 3 is invalid.
#VALUE!: One or more arguments may be invalid, such as a column index that is not a usable positive number. Check the formula arguments and the value supplied for the column number.
#NAME?: Excel may not recognize the function name because it is misspelled, or text in the formula may be missing quotation marks. Check spelling and punctuation.

When the formula returns a value but it is the wrong one, inspect the fourth argument and check for duplicates. An exact VLOOKUP returns the first matching row it encounters, not every match. If a key appears more than once, the first occurrence in the selected range determines the result.

Fix an incorrect column number or range

The column number counts columns from the left edge of the selected range, not from the worksheet’s column A. If the range is C:F, then C is column 1, D is 2, E is 3, and F is 4. A common editing mistake is changing the range but leaving the old column number in the formula.

Check that the range includes both the lookup column and the return column, and that its top and bottom rows include all relevant data. If new rows were added below a fixed range, the formula may simply not include them. Convert the source data into an Excel table using Insert > Table, then use the table as the source so it can expand as rows are added. Confirm that the table still contains the columns the formula expects.

Absolute references are also important when copying a formula. Without dollar signs, a range such as A2:B100 can shift as the formula is filled down. Fix the table range as $A$2:$B$100 while leaving the lookup cell relative if each row should search for its own value. In Excel for Microsoft 365 on Windows, dragging the fill handle or copying and pasting fills formulas; the reference behavior is the same on Mac, though keyboard shortcuts can differ.

Check numbers, text, spaces, and other hidden differences

Values that look identical on screen may not be identical to Excel. A number such as 123 stored as text will not always match the numeric value 123. Imported data is a common source of this problem: one list may contain numeric IDs while another contains text IDs. Check the source cells’ data types and, where appropriate, convert both sets consistently. Changing a cell’s number format alone changes how a value appears; it does not necessarily convert text into a number.

Spaces and invisible characters can also prevent a match. A name such as “Ava Lee” with a trailing space is different from “Ava Lee” without one. Data copied from websites or other systems can contain nonbreaking spaces or other nonprinting characters that ordinary trimming may not remove. Compare the source and lookup values carefully, and clean the data in a helper column if needed. Excel’s TRIM function removes regular extra spaces but does not remove every kind of invisible character.

For example, if the text key to clean is in A2, put this in a helper cell and fill it down:

=TRIM(A2)

This removes leading and trailing regular spaces and reduces repeated spaces between words to single spaces. Apply a consistent cleanup to the lookup value too; cleaning only one side may leave the values different. Keep the original data until you have checked that the cleaned keys still distinguish records correctly.

Use exact matching and handle missing values deliberately

FALSE is the clearest choice for exact matching. It tells Excel not to treat the lookup value as a point within a range. Exact matching does not mean that VLOOKUP ignores data quality: text-versus-number differences, extra characters, and missing keys can still prevent a match.

If a missing key is an ordinary possibility, you can show a message rather than an error. With lookup IDs in A2:A100, return values in B2:B100, and the requested ID in D2, use:

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

IFNA catches #N/A and replaces it with the specified text. It does not hide other errors such as #REF!, which is useful because a broken column index should be fixed rather than disguised as a missing record. Be cautious about wrapping every formula in a broad error handler: hiding errors can make a faulty workbook look correct.

Compare VLOOKUP with other lookup options

VLOOKUP remains useful for straightforward tables where the lookup key is in the first column and the result is to its right. Its structure is familiar, and it works in older Excel versions. Its main constraints are the leftmost-column requirement and the numeric return-column index, which can become fragile if the table layout changes.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup range from the return range, can return results from either side, and uses exact matching by default. For older Excel versions, INDEX and MATCH can also search one range and return from another. With IDs in B2:B100, names in C2:C100, and the ID to find in E2, use:

=INDEX($C$2:$C$100,MATCH(E2,$B$2:$B$100,0))

MATCH’s final 0 requests an exact match, and INDEX returns the value at the matched position in the names range. If you only need a basic rightward exact lookup and compatibility matters, VLOOKUP is still a reasonable choice. If the lookup column may move or the result is to its left, choose XLOOKUP or INDEX/MATCH where your Excel version supports it.

A quick troubleshooting sequence

Work through these checks in order; the first few resolve many everyday failures:

Read the error, or compare the returned value with the source row you expected.
Confirm the lookup key is in the leftmost column of the selected range.
Use FALSE for an exact match unless you deliberately need approximate matching.
Verify the range includes the key and result columns, all relevant rows, and the correct column index.
Compare the lookup value with the source value for type, spelling, spaces, and hidden characters.
Check whether the key appears more than once and whether the first occurrence is the intended result.
Test the formula on a known key, then copy it down and check that the ranges stay fixed.

For a useful test, choose a key whose source row you can identify without relying on the formula. If that test works but other rows fail, investigate differences in the failing keys or whether those records fall outside the selected range. If every row returns the same unexpected result, inspect the match setting and the formula’s range before editing the data.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?

The displayed values may differ underneath: one may be text and the other a number, or one may contain an extra space or invisible character. Also check that the searched value is in the first column of the selected range and that the formula covers the row containing it.

Why does VLOOKUP return the wrong result?

Check whether the formula uses approximate matching because its final argument is TRUE or omitted. For exact lookups, use FALSE. If FALSE is already present, check for duplicate keys; VLOOKUP returns the first matching row in the range.

Can VLOOKUP return a value from a column on the left?

No. VLOOKUP searches the leftmost column of its table range and returns from a column to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or INDEX/MATCH where available, when the result is to the left.

Why does VLOOKUP show #REF!?

The return-column number may be greater than the number of columns in the selected range, or a referenced range may have been deleted. Count the return column from the left edge of the table range and check that the range still exists.

Does VLOOKUP distinguish uppercase and lowercase?

No. VLOOKUP’s text matching is not case-sensitive, so values that differ only in capitalization are treated as matches. If capitalization must distinguish records, 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.