VLOOKUP Not Working? Causes and Fixes

If VLOOKUP is not working, set its final argument to FALSE for an exact match, then check that the lookup value appears in the first column of the selected range and uses the same data type. Those checks solve many #N/A errors and incorrect results; the error message and formula structure can help identify what to fix next.

How VLOOKUP works

VLOOKUP searches for a value in the leftmost column of a selected range, then returns a value from another column on the same row. Its arguments specify what to search for, where to search, which column to return, and whether to use an exact or approximate match. The column number is counted from the start of the selected range—not from the worksheet’s column letters.

For example, if the lookup value is in E2, product IDs are in column A, and prices are in column C, the selected range begins in A and the price is its third column. With product ID P-104 in E2 and a matching price of $18.50 in C, an exact-match lookup returns $18.50.

With the lookup value in E2, product IDs in A2:A100, and prices in C2:C100, use this formula to search A2:C100 and return its third column:

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

VLOOKUP can return values only from columns to the right of the column it searches. It also returns the first matching row if the lookup column contains duplicates. These are limits of the function, not necessarily signs that the formula is broken.

A quick diagnostic sequence

Before rebuilding a formula, check it in a consistent order. This can distinguish a bad match from a wrong return column or a range that moved while the formula was copied.

Click the formula cell and inspect the formula bar. Confirm that the lookup value is the cell you intended.
Check that the first column of the selected range contains the values you want to search. The lookup column must be at the left edge of the range.
Confirm that the final argument is FALSE if you want an exact match.
Compare the lookup value and source values for data type, leading or trailing spaces, and other hidden characters.
Check that the column index points to the intended column and is no greater than the range’s width.
If the formula works in one row but not after copying it, inspect whether the range shifted or the row reference changed unexpectedly.

After each check, recalculate or press Enter in the formula bar if you edited the formula. Avoid changing several things at once: changing one cause at a time makes it easier to identify what actually fixed the result.

Exact match versus approximate match

The fourth argument controls an important source of surprising results. FALSE requests an exact match. TRUE requests an approximate match, and leaving this argument out also makes VLOOKUP use approximate matching. As a result, a formula can return a value without showing an error even though the lookup key is not present exactly.

Approximate matching is useful for sorted thresholds, such as assigning a band based on a score or rate. In that case, the first column needs to be sorted in ascending order, and the returned result represents the applicable lower threshold. For ordinary identifiers—product codes, employee IDs, invoice numbers, or names—an exact match is usually the appropriate choice. Do not rely on sorted data to make an approximate lookup safe if the values are not deliberately organized for that purpose.

When you copy formulas down a list, verify the match setting in every formula or use a consistent formula pattern. A missing final argument can be easy to overlook, especially when a formula was built by editing an older one. If a lookup returns a plausible but incorrect row, checking for an omitted or TRUE match argument is a good early step.

Numbers stored as text, spaces, and hidden characters

Two values can look identical on screen and still fail an exact match because Excel stores them differently. For example, the number 104 and the text value “104” are different data types. This often happens when one column was imported from a text file or copied from another system. Number formatting alone does not necessarily convert text into a real number.

Compare both sides of the lookup, not just the formula cell. If one side is numeric and the other is text, convert the source values consistently. You can use Excel’s warning menu to convert numbers stored as text, or create a helper column that converts text digits to numbers. For example, with a numeric-text lookup value in E2 and numeric IDs in A2:A100, this formula converts E2 before searching:

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

This works only when E2 contains text that VALUE can interpret as a number; it is not suitable for codes such as P-104. For codes, keep both columns as text and preserve meaningful leading zeros. Converting a code such as 00104 into a number can remove those zeros and change the identifier.

Spaces can also cause mismatches. A trailing space after an ID may be difficult to notice, and copied data can contain nonprinting characters. TRIM removes ordinary extra spaces from text, while CLEAN removes certain nonprinting characters; neither automatically fixes every kind of imported whitespace. A helper column can make cleaned values visible and easier to compare. Be careful not to clean or convert identifiers in a way that changes their meaning.

Check the lookup range and column index

The range must include both the lookup column and the column containing the result. If the lookup values are in B and the return values are in D, a range beginning at B counts B as column 1, C as column 2, and D as column 3. Using 4 because D is the worksheet’s fourth column would point to the wrong place—or outside the selected range.

When a formula is copied, relative references can shift. With the lookup value in E2 and the source data in A2:C100, an unlocked range may move as the formula is filled into other rows. Lock the data range with dollar signs while allowing the lookup reference to move by row:

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

In this setup, copying the formula down changes E2 to E3, E4, and so on, but keeps the source range fixed. If the lookup value itself should stay in E2, lock that reference too. A range that ends at row 100 will not include new data added below row 100; an Excel Table can expand as rows are added and may be a better fit for a growing dataset.

Also check that the returned column is to the right of the lookup column. VLOOKUP cannot search in C and return a value from A using a range that starts at A; it always searches the range’s first column. If the table layout cannot be changed, consider a different lookup method.

What common error messages mean

#N/A: Excel did not find a matching value under the selected match rule. Check exact versus approximate matching, spelling, data type, spaces, and whether the lookup column is the first column in the range.
#REF!: The requested column number is outside the selected range, or a referenced range has been deleted or otherwise become invalid. Recount the columns from the range’s left edge.
#VALUE!: The column index is invalid, such as being zero or negative. Confirm that it is a positive whole-number column position.
#NAME?: Excel may not recognize the function name or part of the formula. Check for a misspelling, missing punctuation, or text arguments that were entered incorrectly.

If the cell displays the formula itself instead of a result, check whether the cell is formatted as Text. Change it to General, then re-enter the formula. Also check that Show Formulas has not been turned on; that setting displays formulas across the worksheet rather than their calculated results.

Why VLOOKUP returns the wrong value

A wrong result is not always a failed match. With approximate matching, an unsorted lookup column can produce an unexpected result. With exact matching, duplicate keys make VLOOKUP return the first matching row from top to bottom, so a later duplicate will not be selected automatically. If duplicates are possible, decide which row should win or use a method that explicitly handles multiple matches.

Another issue is an incorrect return-column index. The formula may find the right key but pull a value from the neighboring column. Compare the result with the source row and count the selected range’s columns from left to right. Also confirm that the source data has not been sorted in a way that separated related information across rows.

If a correct formula shows an old answer after the source data changes, check calculation mode. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. In manual calculation mode, formulas may not update until recalculation. If only one formula seems stale, press F9 to recalculate the workbook; on many Mac keyboards, you may need Fn+F9. Keyboard behavior can depend on the Mac’s function-key settings.

When to use XLOOKUP or another alternative

VLOOKUP remains useful for straightforward tables, especially when a workbook needs to work in older Excel versions. But it is less flexible when the return column is to the left, and its numeric column index can become fragile if columns are inserted or rearranged.

In Microsoft 365, XLOOKUP can search one range and return a value from a separate range, including a column to the left. It also uses exact matching by default. For example, if the lookup key is in E2, the keys are in C2:C100, and the desired results are in A2:A100, this formula returns the corresponding value from column A:

=XLOOKUP(E2,C2:C100,A2:A100)

XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases. INDEX with MATCH is another flexible option in older versions, though it takes more setup. Choose based on the workbook’s compatibility needs, whether the return column is left or right of the keys, and whether duplicate matches need special handling.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is there?
Check whether the two values are stored as different data types, contain extra spaces or hidden characters, or differ by a small spelling or punctuation detail. Also confirm that the lookup column is the first column in the selected range and that an exact-match formula is being used.

Why does VLOOKUP return the wrong row?
The formula may be using approximate matching, which can return an unintended result if the lookup column is not sorted as required. If exact matching is already in use, check the column index and look for duplicate keys; VLOOKUP returns the first matching row.

Does VLOOKUP search to the left?
No. VLOOKUP searches the first column of its selected range and returns a value from a column to its right. Use XLOOKUP in supported Excel versions, or INDEX and MATCH, when the return column is to the left.

Why does my VLOOKUP formula change when I copy it?
Excel adjusts relative references when formulas are copied. Lock the lookup range with dollar signs so it stays fixed, while leaving the lookup cell relative if it should advance down each row.

Can VLOOKUP return more than one match?
VLOOKUP returns one result: the first matching row. If you need every matching record, use a filtering or other multi-result approach available in your Excel version instead of expecting VLOOKUP to list duplicates.

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.