Why Is VLOOKUP Not Working in Excel?

VLOOKUP usually fails because its lookup range, match setting, or data types do not match the data you are searching. Check that the value is in the range’s first column, use FALSE for an exact match, and confirm that the lookup value and table key are stored in the same way.

How VLOOKUP searches a table

VLOOKUP searches down the first column of a selected range, finds a matching value, and returns a value from another column on the same row. Its arguments specify the lookup value, the table range, the number of the return column within that range, and whether to use an exact or approximate match. The column number is counted from the left edge of the selected range, not from the worksheet’s column A.

For example, suppose A2:A5 contains product codes, B2:B5 contains product names, and F2 contains the code to find. With those cells laid out that way, this formula looks for an exact match and returns the corresponding product name:

=VLOOKUP(F2,A2:B5,2,FALSE)

If A2:A5 contains P-104, P-208, P-311, and P-427, B2:B5 contains Desk, Lamp, Chair, and Shelf, and F2 contains P-311, the result is Chair. VLOOKUP cannot return a value to the left of its search column: in this example, it can return a name from column B, but it cannot find the product name in B and return the code from A with the same range arrangement.

Use the right match setting

A frequent cause of an unexpected result is leaving out the fourth argument or setting it to TRUE. TRUE, and an omitted fourth argument, request an approximate match. That mode is intended for ranges such as tax brackets or grading bands, and it assumes the first column is sorted in ascending order. If the values are unsorted, the result may be wrong rather than a clear error.

For ordinary lookups such as an employee ID, order number, or product code, use FALSE to require an exact match. Exact matching does not require the first column to be sorted. It returns the first exact match it encounters, so repeated keys can still produce a result from the wrong-looking row.

With product codes in A2:A5, product names in B2:B5, and the code to search in F2, the formula below requires an exact match. If F2 contains P-311, it returns Chair; if no cell in A2:A5 exactly matches, it returns #N/A.

=VLOOKUP(F2,A2:B5,2,FALSE)

Check the range and return-column number

The table range must start with the column that contains the lookup keys. If the keys are in column C and the results are in D, a range beginning at A will make VLOOKUP search the wrong field. Select a range that begins at C instead. Also verify that the return-column number is between 1 and the number of columns in the range. A value of 1 returns the first column; a value of 2 returns the second.

For a larger table, an absolute reference prevents the range from sliding when you copy the formula down. With lookup IDs in F2:F100, IDs in A2:A100, and results in C2:C100 (the third column of A:C), enter this in the first result row and copy it down. If F2 matches A2, it returns the value in C on that matching row.

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

Absolute references do not fix an incorrect range or column number. If you insert or rearrange columns in a workbook, check that the numbered return column still points to the intended data. A column number that exceeds the range width produces #REF!, while a valid but incorrect number can quietly return a different field.

Look for differences in the underlying values

Two cells can look identical while Excel treats them as different. One may contain a number and the other text that looks like a number. For example, a numeric 104 and a text value “104” are not reliably interchangeable in an exact lookup. Imported data often has this issue because a system or file stores identifiers as text. Decide which format the column should use, then standardize both the lookup value and the keys. For identifiers with leading zeros, text may be intentional: converting “0042” to a number would lose those zeros.

With a text-formatted number in F2, numeric keys in A2:A100, and matching results in B2:B100, this formula converts F2 to a number before searching. If F2 contains the text 104 and A7 contains numeric 104, it returns B7’s value. This approach only suits values that should genuinely be numbers.

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

Extra spaces are another common culprit, especially in data copied from websites or exported from other systems. A leading or trailing space can prevent an exact match. With the search text in F2 and the lookup keys in A2:A100, the formula below trims ordinary spaces from the lookup value before searching; if the cleaned value matches A7, it returns B7.

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

TRIM on F2 does not clean spaces in the lookup column. If the source keys are dirty, clean that column too, ideally in a helper column or at the data-import stage. Some copied text contains nonbreaking spaces, which TRIM may not remove. Check troublesome values by comparing their lengths or inspecting and cleaning the source data rather than assuming every invisible character is a regular space.

Understand the error or wrong result

The displayed result often narrows down the cause. #N/A means an exact match was not found, or an approximate lookup did not find a suitable value. Check spelling, spaces, number-versus-text formats, and whether the lookup value is actually inside the selected first column. #REF! usually points to an invalid return-column number or a broken reference. #VALUE! can indicate an invalid argument, including a return-column number below 1. A #NAME? result can appear if a function name or text in the formula is misspelled.

To show a more helpful message when the lookup does not find a match, use IFNA. With the lookup value in F2, keys in A2:A100, and results in B2:B100, the formula returns the corresponding result when found and Not found when VLOOKUP returns #N/A:

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

IFNA only handles #N/A; it does not hide every possible formula error. That is useful when you want a missing key to be understandable without masking a broken range or invalid column number. Avoid replacing errors with a blank until you have diagnosed the cause, because an empty display can make incomplete or incorrect data harder to spot.

Check duplicates, wildcards, and case

VLOOKUP returns the first matching row it finds, from top to bottom. If the lookup key occurs more than once, the result will come from the first occurrence, which may not be the row you intended. Sort order does not change which duplicate an exact-match lookup finds. Inspect the key column for repeated values and decide whether the key should be unique or whether the workbook needs a method that can return all matches.

Exact matching with FALSE is not case-sensitive: codes ABC and abc are treated as matching text. If capitalization distinguishes records, VLOOKUP alone is not enough to enforce that distinction. Exact-match VLOOKUP also recognizes the wildcard characters * and ? in the lookup value. A tilde can be used to search for a literal wildcard character. These details matter when a key itself contains wildcard symbols or when a search string includes them unintentionally.

A practical troubleshooting sequence

When a formula appears not to work, change one thing at a time instead of replacing the formula at random. Start with the formula bar and inspect each argument, then test the underlying key values. This sequence catches the common causes quickly:

Confirm the lookup value is in the first column of the selected range.
Set the final argument to FALSE for an exact lookup, unless approximate matching is deliberate and the key column is properly sorted.
Count the range columns from left to right and verify the return-column number.
Check whether the lookup value and key cells are both text or both numbers, and look for spaces or other imported characters.
Check for duplicates and confirm the first matching row is the one you want.
Read the exact error, then test with a key you can verify manually in the source table.

If the formula is showing as text instead of calculating, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, then re-enter the formula. Also check that Show Formulas has not been switched on; that setting displays formulas in cells instead of their results. A formula may also appear not to update if calculation is set to manual. In Excel for Microsoft 365 on Windows, these settings are available from the Formulas tab and the cell-format controls on the Home tab.

When XLOOKUP or another method is a better fit

VLOOKUP remains useful for straightforward lookups in tables where the key is in the leftmost column and the return field is to its right. But it is not the only option. XLOOKUP can search in one range and return from a separate range, so the return field can be to either side of the key. It also uses exact matching by default and includes an optional argument for a value to display when nothing is found.

With product codes in A2:A100, product names in B2:B100, and the code to find in F2, this XLOOKUP returns the matching name or Not found. For this example, it returns the name in the row where the code matches. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel editions.

=XLOOKUP(F2,A2:A100,B2:B100,"Not found")

INDEX and MATCH can also look to the left or right, and can be useful when a workbook needs to support older Excel versions that do not have XLOOKUP. If you use VLOOKUP because a workbook is shared across different Excel releases, consider compatibility before changing its formulas. The best choice depends on who must open the file and whether the lookup layout is likely to change.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value is there?
Usually the displayed values differ underneath: one may be text and the other a number, or one may contain leading, trailing, or nonprinting characters. Check the exact-match setting, the first column of the selected range, and the stored values rather than relying on appearance alone.

Why is VLOOKUP returning the wrong row?
Check whether the formula uses TRUE or omits its last argument, which requests approximate matching, and whether duplicate keys exist. For a standard exact lookup, use FALSE and confirm the first matching key is the intended record.

Can VLOOKUP return a value from a column to the left?
No. Its search range must begin with the key column, and its return column must be to the right within that range. Use XLOOKUP, or INDEX and MATCH where appropriate, when the result needs to come from a column on the left.

Why does VLOOKUP show the formula instead of the answer?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be enabled. Set the cell to General and re-enter the formula, and check the Formulas tab for the Show Formulas setting.

Does VLOOKUP distinguish uppercase and lowercase letters?
No. VLOOKUP’s text matching is not case-sensitive, so values such as ABC and abc match. If capitalization is part of the key, 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.