Excel VLOOKUP Not Working? Common Causes and Fixes

Excel VLOOKUP usually fails because the lookup value does not exactly match the first column of the selected range, the formula points to the wrong columns, or Excel is using approximate matching. Check the error or incorrect result, set the final argument to FALSE for an exact match, and confirm the lookup column and cell data types before changing the formula.

How VLOOKUP works

VLOOKUP searches down the first column of a table range for a value, then returns a value from another column in the same row. Its limits explain many problems: the search column must be the leftmost column in the selected range, and the return column is specified by its position within that range—not by its worksheet column letter.

For example, assume product IDs are in A2:A100, product names are in B2:B100, prices are in C2:C100, and the ID to find is in E2. The range A2:C100 has three columns, so 3 asks for the price. With those cells arranged as described, use:

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

If E2 contains an ID found in column A and that row’s price is 12.50, the formula returns 12.50. The dollar signs keep the range fixed if you copy the formula to other rows. FALSE requests an exact match; 0 can be used instead. In Microsoft 365 for Windows, commas separate formula arguments in the examples here. Some regional settings use semicolons instead.

Fix #N/A when the value seems to be present

#N/A means Excel could not find a match under the formula’s current conditions. The value may look identical on screen but differ behind the scenes. Compare the lookup cell with the cells in the first column of the table array, then check these common causes:

Extra spaces: A leading or trailing space makes text different. A value copied from a website may also contain a nonbreaking space or another invisible character.
Different data types: The number 123 and the text value 123 can look alike but do not necessarily match. A green error indicator or inconsistent alignment may help reveal numbers stored as text, though alignment can be changed manually.
Different characters: Hyphens, punctuation, accents, and look-alike characters can differ. For example, a regular hyphen and an en dash are not the same character.
Wrong lookup range: The selected range may omit the row containing the value, or its first column may not be the column where that value is stored.

Inspect the underlying values in the formula bar and check a few cells directly. For imported data, clean or standardize both the lookup values and the source column consistently. TRIM can remove ordinary leading and trailing spaces, but it does not remove every kind of invisible character. Avoid cleaning only one side: a cleaned lookup value still will not match an uncleaned source value.

Also check whether the formula uses exact or approximate matching. If the fourth argument is omitted, VLOOKUP defaults to approximate matching. For IDs, names, product codes, and most ordinary lookups, explicitly use FALSE so Excel does not return a nearby value instead.

Check approximate matching and the final argument

The fourth argument controls how Excel searches. FALSE means Excel must find an exact match. TRUE, or leaving the argument out, allows approximate matching. In that mode, the first column of the lookup range must be sorted in ascending order for reliable results. Excel looks for the largest value that is less than or equal to the lookup value, which can be useful for ranges such as tax brackets or grading thresholds when the table is designed for that purpose.

A frequent misconception is that an omitted argument means exact matching. It does not. A formula may therefore return a plausible but incorrect result rather than an error. If the source column is unsorted, approximate matching can produce an unexpected answer. For a normal exact lookup, use FALSE explicitly and do not rely on the table’s sort order.

VLOOKUP also returns the first matching row it encounters. If the first column contains duplicate IDs, Excel will not automatically choose the newest, largest, or most relevant record. Remove unintended duplicates, make the key unique, or use a method that explicitly handles multiple matches.

Correct the table range and column number

The lookup column has to be the leftmost column of the selected table array. If IDs are in column C and the return values are in column B, selecting B:C will not make VLOOKUP search column C and return B; VLOOKUP cannot search to the left. Rearrange the source data, select a range that starts with the lookup column, or choose a different lookup method.

The column index counts from the first column of the selected range. If the range is B:D, then B is 1, C is 2, and D is 3—even though D is worksheet column 4. A column index greater than the number of columns in the range produces #REF!. A zero or negative index is invalid. When you insert or rearrange source columns, review the index: a formula may continue working while returning a different field than intended.

When copying a formula down, lock a fixed source range with dollar signs, as in the example above. Without them, a relative range can move from A2:C100 to A3:C101 in the next row, potentially excluding relevant records or changing the lookup area. Use F4 while editing a cell reference in Excel for Windows to cycle through reference styles; on many Mac keyboards, use Command+T to cycle a selected reference, though keyboard settings can affect the shortcut.

Understand other VLOOKUP errors

#REF!: The requested return-column index is outside the selected range, or a referenced column or range was deleted. Recheck the range and count its columns from left to right.
#VALUE!: An argument may be invalid, such as a return-column index below 1. Check that the index is a valid positive number and that the arguments are in the intended order.
#NAME?: Excel may not recognize the function name or a name used in the formula. Check spelling and any named ranges. A missing equals sign or an unclosed parenthesis can cause other formula-entry problems.
Formula displays instead of a result: The cell may be formatted as Text, the formula may have been entered with a leading apostrophe, or Show Formulas may be on. Change the cell to General, re-enter the formula, and check Formulas > Show Formulas.

For a missing item, you can display a clearer message instead of #N/A. With product IDs in A2:A100, prices in C2:C100, and the search ID in E2, this formula returns the price when found and Not found otherwise:

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

IFNA handles #N/A specifically, leaving other errors visible so you can diagnose problems such as #REF!. It does not repair a lookup; it only changes how a not-found result is displayed.

A practical troubleshooting sequence

Work from the lookup conditions toward the formula structure. This prevents a cosmetic workaround from hiding a bad match or wrong return column.

Click the result cell and read the formula bar. Confirm the lookup cell, selected range, return-column index, and final match argument.
Set exact matching with FALSE unless approximate matching is deliberately required for a sorted threshold table.
Confirm the search value is in the first column of the selected range, and verify the return index points to the intended field.
Search the source column for the exact value. Check for spaces, text-versus-number differences, and characters that merely look alike.
Check for duplicate keys and verify that the row Excel returns is the one you expect.
For a difficult case, use Formulas > Evaluate Formula to step through the calculation on Windows. The feature is also available from the Formulas tab in Excel for Mac, though the ribbon layout can vary.

Test a known value that should match and a value that should not. If the known value fails, investigate the source data or range. If it succeeds but the wrong field appears, check the column index. If only some rows fail after copying down, inspect the references and data consistency in those rows.

When to use XLOOKUP instead

VLOOKUP remains useful in older workbooks, but XLOOKUP avoids two structural limitations: its lookup and return ranges can be separate, and it can search in either direction. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. With product IDs in A2:A100, prices in C2:C100, and the ID to find in E2, this formula returns the matching price or a message if there is no match:

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

XLOOKUP uses exact matching by default, so a match-mode argument is not needed for this example. If a workbook must be opened in an older Excel version that does not support XLOOKUP, use VLOOKUP or INDEX with MATCH instead. Do not replace a working formula just to use a newer function if recipients need compatibility with older software.

Frequently Asked Questions

Why does VLOOKUP return the wrong value instead of #N/A?
If the final argument is omitted or TRUE, VLOOKUP uses approximate matching. Use FALSE for an exact match, and remember that an exact duplicate key returns the first matching row.

Why does VLOOKUP not find a number stored as text?
Excel can treat a text string such as 123 differently from the numeric value 123. Convert the source and lookup values to the same data type, then check for spaces or other hidden characters if the mismatch remains.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the leftmost column in the selected range, and VLOOKUP returns values from columns to its right. Reorder the range or use XLOOKUP or INDEX with MATCH when searching left is necessary.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s ordinary text matching is not case-sensitive, so values that differ only by capitalization are treated as a match. If case matters, use a case-sensitive approach rather than relying on VLOOKUP alone.

Why does VLOOKUP stop working after I add a column?
The formula may still run, but its numeric return-column index can now point to a different field. Recheck the table range and count the intended return column from the range’s left edge. XLOOKUP uses a separate return range and is less dependent on a column number.

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.