VLOOKUP Match Not Working? Fix Common Causes

If VLOOKUP is not finding a match, first use exact-match mode by setting its fourth argument to FALSE, then check that the lookup value and the first column of the lookup range contain the same kind of data. In Excel for Microsoft 365 on Windows, a typical exact-match formula is =VLOOKUP(E2,$A$2:$C$100,3,FALSE); it looks for E2 in column A and returns the corresponding value from column C.

How VLOOKUP matching works

VLOOKUP searches vertically down the first column of a range. Its syntax is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The first three arguments identify what to search for, where to search, and which column to return. The optional fourth argument controls the match type.

For an exact match, use FALSE or 0. Excel looks for a value that matches the lookup value; the search column does not need to be sorted. If you omit the fourth argument, VLOOKUP uses approximate matching (TRUE). Approximate matching expects the first column to be sorted in ascending order and can return an unexpected result when it is not. This omitted-argument behavior is a frequent reason a formula appears to match the wrong item.

For example, with product codes in A2:A100, prices in C2:C100, and the code to find in E2, this formula returns the price for an exact code match:

With codes in A2:A100, prices in C2:C100, and the lookup code in E2, use: =VLOOKUP(E2,$A$2:$C$100,3,FALSE). If E2 is P-104 and the matching row has 18.5 in column C, the result is 18.5.

Check the match mode and lookup range

Before cleaning data or rebuilding a workbook, confirm that the formula is searching the intended column and using the right match mode. The lookup column must be the leftmost column in the selected table range. VLOOKUP cannot search in a column to the right and return a result from a column to its left.

Set the final argument to FALSE for exact matching. FALSE and 0 both request an exact match.
Make sure the first column of the selected range is the column containing the lookup values.
Check that the return-column number counts from the start of the selected range, not from worksheet column A.
When copying a formula down, lock the table range with dollar signs so it does not shift.

In the example range A2:C100, column A is index 1, B is index 2, and C is index 3. A column index of 2 would return column B, even if you meant to return column C. A wrong index can produce a believable but incorrect result rather than an error. Also, a fixed range without dollar signs may move as you fill the formula down: A2:C100 can become A3:C101. The reference $A$2:$C$100 stays fixed.

Look for text-versus-number mismatches

Values that look identical on the sheet may be stored differently. A cell can contain the number 1042, while another contains the text string “1042.” Exact lookup can fail when the lookup value and the search-column value have different data types. This often happens with imported CSV files, copied data, or codes that were formatted inconsistently.

Click a cell and inspect the formula bar, number format, and alignment, but do not rely on alignment alone: formatting can make text and numbers look alike. You can also test a particular cell. With the value being checked in A2, =ISNUMBER(A2) returns TRUE if it is numeric; =ISTEXT(A2) returns TRUE if it is text.

If A2 contains text that represents a number and the intended lookup values are numeric, a helper column can convert it. With the text number in A2, =VALUE(A2) returns the numeric value 1042 when A2 contains “1042.” Confirm that conversion is appropriate before applying it to identifiers: converting codes to numbers can remove meaningful leading zeros.

For example, the text code “00127” and the number 127 are not interchangeable identifiers if the zeros matter. Keep such codes as text on both sides of the lookup. If the source contains numeric values but the required identifier format has five digits, you can create a text key. With the numeric code in A2, =TEXT(A2,"00000") returns the text “00127” when A2 contains 127. Use the same format consistently for the lookup value and the table’s key column.

Remove spaces and invisible characters

Leading or trailing spaces can prevent a text key from matching. A copied value may also contain a nonbreaking space, which is visually similar to an ordinary space but is not removed by TRIM alone. Other imported characters can be difficult to see, so compare the source values carefully before assuming they are identical.

For a simple case, TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. With the unclean text in A2, =TRIM(A2) creates a cleaned version. CLEAN can remove many nonprinting characters, but it does not remove every character that may arrive from an external source.

For a common nonbreaking-space problem, use a helper formula that replaces character 160 with a normal space, removes control characters, and trims the result. With the source text in A2, =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) returns a normalized text value. Apply equivalent cleanup to the lookup value and the table’s lookup column; cleaning only one side will not make unlike values match. Keep the original data in a separate column until you have checked the results.

Understand errors and unexpected results

The error or result can narrow down the cause. Check the formula bar for the actual formula and inspect the matched row, rather than relying only on what appears in the cell.

#N/A: No exact match was found. Check data type, spaces, spelling, punctuation, and whether the lookup value exists in the first range column.
#REF!: The column index points beyond the selected range, or a referenced range was deleted or changed.
#VALUE!: An argument may be invalid, including a column index that is less than 1.
#NAME?: Excel may not recognize a function or text in the formula; check spelling and quotation marks.
A plausible but wrong value: Check for an omitted fourth argument, a wrong column index, approximate matching, or duplicate keys.

VLOOKUP returns the first matching row it encounters when duplicate keys exist. If the table contains the same ID more than once, the first result is expected behavior, not proof that Excel searched the wrong row. Decide whether the key should be unique or whether the workbook needs a method that returns multiple matching records.

A result of 0 is not automatically a failed match. The matched return cell might contain zero, or it might be blank and display as zero under the formula’s circumstances. Check the source row and the cell’s number format. If you want to display a message only when no match exists, handle the error rather than changing the lookup mode.

With E2 as the lookup value and A2:C100 as the table, =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found") displays Not found when the exact lookup produces #N/A. IFNA does not repair a bad key or wrong range; it only changes how that particular error is displayed.

Check formula entry and recalculation

If the cell displays the formula text instead of a result, Excel may be treating the entry as text. Select the cell and check Home > Number Format; change Text to General, then re-enter the formula. A leading apostrophe can also mark an entry as text. Another possibility is that Show Formulas is enabled, in which case formulas are displayed across the worksheet instead of their results.

If the formula remains unchanged after source data changes, check Formulas > Calculation Options and choose Automatic if the workbook is set to Manual. In Manual calculation mode, formulas may not recalculate until calculation is requested. Also confirm that the source values really changed in the cells the formula references, particularly when data is imported or refreshed into another range.

When copying a working formula, inspect its references in the destination row. The lookup-value reference may need to move from E2 to E3, while the lookup table should remain fixed at $A$2:$C$100. Mixed or missing dollar signs can make a formula work in the first row and fail later.

Choose an alternative when VLOOKUP is the limitation

Sometimes the match is valid but VLOOKUP is the wrong tool. It cannot return a value from a column to the left of its lookup column, and its numeric return-column index can become fragile when table columns are inserted or rearranged.

In Microsoft 365 and Excel 2021 or later, XLOOKUP can search one range and return from another, including a column to the left. It uses exact matching by default, and its not-found argument can provide a message. With lookup values in A2:A100, return values in C2:C100, and the key in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found",0). If E2 is not present, the formula returns Not found. XLOOKUP is not available in some older Excel versions, so a workbook shared with those versions may show an unsupported-function error.

INDEX and MATCH are another option and work in older Excel versions as well. With keys in A2:A100, return values in C2:C100, and the lookup key in E2, =INDEX(C2:C100,MATCH(E2,A2:A100,0)) returns the value from column C on the row where MATCH finds E2. The 0 in MATCH requests an exact match. This arrangement can return values from either side of the lookup column by changing the INDEX return range.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
Often the lookup key and table key differ in data type, contain extra spaces or hidden characters, or are not exactly the same text. Confirm that the search range starts with the key column and use FALSE for an exact match.

Does VLOOKUP match numbers stored as text?
Not reliably as an exact match. Make the lookup value and lookup column the same type, while preserving text for identifiers whose leading zeros are significant.

Should the fourth VLOOKUP argument be TRUE or FALSE?
Use FALSE for an exact match, which is the usual choice for IDs, names, and product codes. TRUE requests approximate matching and requires the first range column to be sorted in ascending order.

Why does VLOOKUP return the wrong matching row?
Check whether the fourth argument was omitted or set to TRUE, whether the return-column index is correct, and whether the key appears more than once. VLOOKUP returns the first matching row for duplicate keys.

Can VLOOKUP search to the left?
No. Its lookup column must be the first column of its table range, and it returns values from columns to the right. Use XLOOKUP in supported Excel versions or INDEX and MATCH when the return column is to the left.

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.