Why Is VLOOKUP Not Working? Common Causes and Fixes

VLOOKUP usually fails because its match setting is wrong, the lookup value does not match the data exactly, the lookup column is not the first column in the selected range, or the formula points to the wrong cells. Check the formula’s match mode, range, column number, and underlying data before changing the workbook; the error or incorrect result often identifies the cause.

How VLOOKUP Searches a Table

VLOOKUP searches down the first column of a selected table range and returns a value from another column in the same row. Its arguments are the value to find, the table range, the number of the return column counted from the range’s left edge, and an optional match setting. In Microsoft Excel for Microsoft 365 on Windows, an exact-match formula for an ID in E2, IDs in column A, and names in column C is:

With IDs in A2:A100, names in C2:C100, and the ID to find in E2, this formula returns the name in column C for an exact match. If E2 contains 104, and that row’s name is North, the result is North: =VLOOKUP(E2,$A$2:$C$100,3,FALSE)

The range includes columns A through C, so column A is column 1, B is column 2, and C is column 3. The dollar signs keep the range fixed if you copy the formula. Understanding those basics makes it easier to separate a formula problem from a data problem.

Wrong Match Mode: Exact Versus Approximate

The fourth VLOOKUP argument determines what counts as a match. FALSE requests an exact match; TRUE requests an approximate match. If you omit the argument, Excel uses approximate matching, which is a frequent reason a formula returns an unexpected value instead of an error.

Approximate matching is intended for ordered data such as tax bands, grading thresholds, or commission tiers. It assumes the first column is sorted in ascending order. Excel finds the largest value that is less than or equal to the lookup value. If the column is unsorted, the result may be wrong even though the formula displays a plausible answer. If the lookup value is smaller than the first value, approximate matching can return #N/A.

For product codes, names, employee IDs, and most ordinary lookups, use FALSE explicitly. For example, with product codes in A2:A50, prices in B2:B50, and a code in D2, an exact match returns the price on the matching row: =VLOOKUP(D2,$A$2:$B$50,2,FALSE)

Exact matching is not case-sensitive: VLOOKUP treats “ab12” and “AB12” as the same text for this purpose. It can also use wildcards in text searches: an asterisk represents any number of characters, and a question mark represents one character. A tilde can escape a wildcard when you need to search for the literal character. Those behaviors are useful in some cases but can surprise anyone expecting a literal, character-for-character search.

The Lookup Column Is Not at the Left

VLOOKUP can only search the first, or leftmost, column of its table range. It cannot search a column in the middle and return a value from a column to its left. For example, if employee names are in column B and employee IDs are in column A, a range beginning at A cannot use names as the lookup key. Starting the range at B will let VLOOKUP search names, but it cannot then return IDs from A.

Check the first cell of the table range, not just the worksheet’s column letters. If the range is B2:D100, its first column is B, and its second column is C. To return a value to the left of the lookup column, use XLOOKUP in Microsoft 365 or Excel 2021, or use INDEX and MATCH. These approaches are described below.

Incorrect Range or Return-Column Number

The third argument, often called the column index, counts columns within the table range—not columns on the worksheet. If the range starts in column D, then D is 1, E is 2, and F is 3. Counting from the worksheet’s A column leads to the wrong answer.

The index must be at least 1 and cannot be larger than the number of columns in the table range. For instance, asking for column 4 from a three-column range produces #REF!, because there is no fourth column to return. A range that is too narrow can also omit the return data entirely. Expand the table range to include both the lookup column and the column containing the answer, then recalculate the index from the range’s left edge.

Another issue appears when a range is edited. Inserting or deleting columns can change what the index points to, and replacing the index with a number does not make the formula self-documenting. Verify the actual range and the returned field whenever a result looks like valid data but belongs to the wrong column.

Numbers, Text, Spaces, and Other Data Mismatches

Two values can look identical on screen but have different underlying types. A number such as 104 stored as a numeric value is not necessarily an exact match for the text string “104.” Cell formatting alone does not reliably convert one into the other. This often happens after importing a CSV file, pasting data from another system, or mixing manually entered values with imported ones.

Check a sample of the lookup key and the source column. If one side is stored as text, convert both sides consistently rather than applying a quick fix to just one cell. When the lookup key in E2 is text containing a number and the first table column contains real numbers, VALUE can convert the lookup key before searching:

With numeric IDs in A2:A100, names in B2:B100, and a text-formatted numeric ID in E2, this formula returns the matching name; if E2 contains text “104” and row 104’s name is North, the result is North: =VLOOKUP(VALUE(E2),$A$2:$B$100,2,FALSE)

This conversion is appropriate only when the values really are numbers. Identifiers with leading zeros, such as “00104,” are often meant to stay as text; converting them to numbers removes those zeros and changes the key. Decide what the data represents before converting it.

Extra spaces are another common obstacle. A leading or trailing space makes text different from the same text without that space. Nonbreaking spaces copied from websites can be harder to spot and are not always removed by the usual TRIM cleanup. Clean the lookup value and the source column in a consistent way, ideally in helper columns, and check for characters such as nonbreaking spaces when an apparently identical key still fails.

Copied Formulas and Moving References

A formula can work in one row and fail in the next because its references shift when copied. A relative range such as A2:B100 moves as the formula is filled down; after several rows it may no longer include the intended table. Use absolute references for a fixed lookup range, as in $A$2:$B$100, while leaving the lookup cell relative if each row should use its own key.

For a lookup table that grows, consider converting the data range into an Excel table. Select the data and choose Insert > Table, then confirm whether the table has headers. Structured references can make formulas easier to read and can automatically include added rows. Still check that the lookup field is first in the selected lookup range when using VLOOKUP; a table does not remove that limitation.

Also inspect the formula bar rather than relying only on the displayed value. A formula may contain a misspelled sheet name, an unintended space in a text key, or a range that stops one row too soon. If the source data is on another worksheet, verify that the sheet reference names the correct tab and that the referenced cells contain the expected values.

What the Error or Wrong Result Can Tell You

The displayed error narrows the search, but it does not always reveal the full cause. Use these clues as a starting point:

#N/A: An exact match was not found, or an approximate lookup could not find a suitable value. Check spelling, hidden spaces, data types, and whether the value exists in the first column.
#REF!: The return-column index is outside the selected range, or a referenced cell or range has been deleted.
#VALUE!: An argument has an invalid value, such as a column index below 1. Check the arguments and any values supplied by other formulas.
#NAME?: Excel may not recognize the function or a name used in the formula. Check the spelling and any named ranges.
A plausible but incorrect result: Check for omitted FALSE, unsorted data with approximate matching, the wrong index, duplicate keys, or a range that points to a different table than intended.

Exact-match VLOOKUP returns the first matching row it encounters. If the first column contains duplicate keys, it will not automatically return the last match or combine the duplicate records. Check for duplicates when the key appears valid but the returned record is not the one you expected. If duplicates are intentional, choose a method that specifically handles multiple matches.

Wrapping a lookup in IFERROR can replace an error with a message, but it does not correct the lookup. During diagnosis, keep the original error visible so you can distinguish “not found” from a broken reference or invalid argument. After the formula is fixed, an error-handling message may be useful for workbook readers.

Calculation, Workbook, and Data-Source Checks

If a formula appears not to update after you change the lookup value or source data, check Excel’s calculation setting. On Windows, use Formulas > Calculation Options and select Automatic if that is the intended workbook behavior. In Excel for Mac, the calculation options are also available from the Formulas tab; ribbon layout can vary slightly with the app version. Press F9 on Windows to recalculate formulas, or use Fn+F9 on some Mac keyboards. A workbook set to manual calculation can keep showing a previous result until recalculation.

When data comes from a connection or an external workbook, confirm that the source data has refreshed and that the referenced file is available. A formula can be syntactically valid while relying on stale or incomplete data. Also check whether a filter or hidden row has led you to overlook the matching record; ordinary VLOOKUP does not limit its search to visible rows.

A Reliable Troubleshooting Order

Use a short, deliberate sequence instead of rewriting the formula at random. Test the key against the source data first, then inspect the formula’s structure:

Read the full formula and confirm the lookup value is the cell you intend to search for.
Confirm that the first column of the table range contains the lookup keys.
For a normal exact lookup, set the fourth argument to FALSE.
Count the return column from the left edge of the range and make sure that column is included.
Compare the lookup key and a source key for text-versus-number differences, spaces, punctuation, and leading zeros.
Check for duplicate keys, moving references, and a range that excludes relevant rows.
If the formula looks correct but does not update, check calculation settings and the freshness of external data.

When the formula returns the wrong value without an error, start with match mode and the column index. When it returns #N/A, start with the key and first-column data. When it returns #REF!, start with the return-column number and range width. This order helps isolate the fault without masking it with a workaround.

When to Use XLOOKUP or Another Alternative

VLOOKUP remains useful for straightforward lookups when the key is the first column of a table and you want a value to its right. For new formulas in Microsoft 365 or Excel 2021, XLOOKUP can be more flexible: it separates the lookup range from the return range, can search in either direction, and uses exact matching by default. For example, with names in B2:B100, IDs in A2:A100, and a name in E2, this formula returns the matching ID; if E2 is North and North’s ID is 104, the result is 104: =XLOOKUP(E2,$B$2:$B$100,$A$2:$A$100,"Not found")

XLOOKUP is not available in older Excel versions, so a workbook shared with users on an older release may need a compatible formula. INDEX and MATCH can also look left and are supported in many older versions. Whichever method you choose, matching data types and clean keys still matter: changing the lookup function does not fix inconsistent source data.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is in the table?
Check whether the formula searches the correct first column and uses the correct match mode. Then compare the key with the source value for text-versus-number differences, leading or trailing spaces, nonbreaking spaces, and small spelling differences. The values may look alike without being identical.

Why does VLOOKUP return the wrong value instead of an error?
The fourth argument may be omitted or set to TRUE, causing approximate matching. The first column may not be sorted as required, or the column index may point to the wrong field. Duplicate keys can also make VLOOKUP return the first matching row rather than the record you had in mind.

Can VLOOKUP search from right to left?
No. The lookup column must be the leftmost column of the table range, and VLOOKUP returns values from columns to its right. Use XLOOKUP in Microsoft 365 or Excel 2021, or use INDEX and MATCH, when you need to return a value from a column to the left.

Why does VLOOKUP show #REF!?
The requested return-column number may be larger than the number of columns in the selected range, or a referenced range may have been deleted. Count the columns from the range’s left edge and confirm that the return column is included.

Does formatting a number as Text fix a VLOOKUP mismatch?
Not necessarily. Formatting changes how a value is displayed, but it does not reliably change its stored type. Convert the lookup key and source keys to a consistent type, taking care not to remove meaningful leading zeros from identifiers.

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.