VLOOKUP Not Working? Fix Common Excel Lookup Errors

If VLOOKUP is not working in Excel, first set its final argument to FALSE for an exact match, then check that the lookup value is in the leftmost column of the selected range and that the lookup value and table keys use the same data type. These checks resolve many #N/A errors and incorrect results; also verify the column number, range references, and calculation settings.

How VLOOKUP works

VLOOKUP searches down the first column of a table for a value, then returns a value from another column in the same row. Its arguments specify what to find, where to search, which column to return, 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.

For example, suppose A2:A100 contains product IDs, B2:B100 contains product names, and C2:C100 contains regions. Cell E2 contains the ID 1042, whose matching row has West in column C. With that layout, the formula below returns West:

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

The dollar signs keep the table range fixed if the formula is copied to other rows. In Excel for Microsoft 365 on Windows, you can select a cell reference in the formula and press F4 to cycle through absolute and relative reference styles. On a Mac, F4 or Fn+F4 may perform the same action depending on keyboard settings.

The final FALSE requests an exact match. If you leave out the fourth argument, VLOOKUP uses approximate matching by default. That difference is important: approximate matching can return an unexpected row when the first column is not sorted appropriately.

Start with the error message

The displayed error often points to the type of problem. It does not always identify the exact cause, but it helps narrow the checks:

#N/A: Excel did not find a match under the current match settings. Check spelling, spaces, data types, the lookup range, and whether the key exists.
#REF!: The requested return-column number is outside the selected range, or a referenced column or range was deleted.
#VALUE!: An argument may be invalid, including a column index that is less than 1.
#NAME?: Excel may not recognize the function name, often because of a typo or because the formula was entered incorrectly.

If the formula returns a value but it is the wrong value, focus first on the match mode, sort order, and column index. An incorrect result can be harder to spot than an error because Excel may have successfully calculated a lookup using unintended rules.

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

First confirm that the lookup key is actually in the first column of the range. VLOOKUP searches only that leftmost column; it cannot search a column to the right and return a value from a column to its left. Also check whether the range begins too low or ends before the row containing the key.

Next, compare the actual values rather than their appearance. A number stored as text can look identical to a number stored as a numeric value but fail an exact lookup. This commonly happens with imported IDs, postal codes, and data copied from websites or text files. Leading zeros add another complication: an ID such as 00427 may be meaningful text, while the numeric value 427 is different for lookup purposes.

Spaces and hidden characters are another common cause. A key copied from another system may contain a trailing space or a nonbreaking space that is not obvious on screen. Check the source data and clean the lookup keys consistently. TRIM removes ordinary leading, trailing, and repeated spaces, but it does not remove every possible invisible character, and it does not automatically clean the table column just because it is used on the lookup value.

Finally, verify that the key exists in the source data and that the formula uses FALSE for an exact match. If you want to show a friendlier message instead of #N/A, you can wrap the lookup in IFNA. With the same IDs in A2:A100, regions in C2:C100, and the search ID in E2, this formula displays Not found when there is no exact match; it returns West when E2 is 1042:

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

IFNA changes what the worksheet displays when no match is found; it does not repair a wrong range, mismatched data type, or other formula problem. Use it after checking the lookup, so a genuine issue is not concealed by a message.

Check the range and return-column number

The third argument is a position within the selected range. If a range begins in column B, then B is column 1, C is column 2, and D is column 3. A formula that asks for column 4 from a three-column range produces #REF!, because that return column is not included.

When copying a formula down, make sure the table range does not shift. In the example, the dollar signs in $A$2:$C$100 prevent the range from moving as the formula is copied. Without fixed references, a copied formula might search A3:C101, then A4:C102, potentially excluding the first rows of the source data and creating inconsistent results. You can also use an Excel table as the source so its structured references expand when rows are added.

Review the third argument whenever you insert, delete, or rearrange columns. VLOOKUP stores a number such as 3, not a label such as Region. If the table structure changes, the third column may no longer hold the intended result even though the formula still calculates without an error.

Choose the correct match mode

For IDs, names, product codes, and most ordinary lookups, use FALSE to request an exact match. TRUE, or an omitted fourth argument, requests approximate matching. In approximate mode, the first column should be sorted in ascending order; Excel looks for an exact match or, if it cannot find one, the nearest lower value. With unsorted data, approximate results may be unreliable or misleading.

Approximate matching is useful for intentional threshold tables, such as assigning a category based on a score or selecting a rate band. In those cases, the boundaries and sort order must be designed for that purpose. Do not use approximate matching just because it makes a formula return something: an approximate result is not a substitute for resolving why an exact key cannot be found.

When the formula does not update

If you change a lookup value but the answer stays the same, check Excel’s calculation mode. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. In Manual mode, formulas may not recalculate until you request it. Press F9 on Windows to recalculate; on a Mac, use the calculation command available from the Formulas menu or the keyboard shortcut supported by your keyboard setup.

Also check whether the cell is formatted as Text. A formula entered into a text-formatted cell may appear literally instead of calculating. Change the cell format to General, then re-enter the formula—for example, select the formula bar and press Enter. Look for a leading apostrophe before the equals sign, which also makes Excel treat the entry as text. If the cell displays the formula rather than its result, make sure Show Formulas is not turned on; the Windows shortcut is Ctrl+` (the grave-accent key), with the equivalent key combination varying by Mac keyboard.

Use a lookup that fits the task

VLOOKUP remains useful when the lookup key is in the first column of a table and the return value is to its right. Its main structural limitation is that it cannot return a value from a column to the left of the lookup column. It also uses a numeric column index, which can become fragile if someone rearranges the source columns.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can search one range and return a result from a separate range, including a range to the left, and it uses exact match by default. With IDs in A2:A100, regions in C2:C100, and the lookup ID in E2, the following formula returns West for ID 1042 and Not found if the key is absent:

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

INDEX and MATCH are another option when a workbook needs compatibility with older Excel versions or when you want separate control over the lookup and return ranges. For a straightforward exact lookup in a modern workbook, however, correcting VLOOKUP’s range, match mode, and data consistency is often the fastest solution.

A practical troubleshooting sequence

Work through these checks in order, changing one thing at a time so you can identify what caused the problem:

Read the error or compare the returned value with the expected row.
Confirm the lookup value is in the range’s first column and that the matching row is included.
Set the last argument to FALSE unless approximate matching is deliberate.
Check that the lookup key and source keys have the same type and content, including spaces and leading zeros.
Count the columns in the selected range and confirm the return-column number points to the intended result.
Fix the range with absolute references before filling the formula down.
Check cell formatting and calculation mode if the formula displays as text or fails to update.

For a large workbook, test with a key that you can verify directly in the source table. Once that test works, check several different keys, including one near the beginning and one near the end of the data. This can reveal a range that accidentally omits some rows.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
Usually the values differ in a way that is not obvious, such as a number stored as text, an extra space, or a hidden character. Confirm that the search value is in the first column of the selected range and that the formula uses an exact match when appropriate.

Why does VLOOKUP return the wrong row?
Check whether the fourth argument is missing or TRUE. That selects approximate matching, which depends on sorted data and may return a nearby lower value. For a unique ID or name, use FALSE for an exact match.

Can VLOOKUP look to the left?
No. The lookup column must be the first column in the selected table range, and VLOOKUP returns values from columns to its right. XLOOKUP can return from a separate range on either side in supported Excel versions.

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

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s text matching is not case-sensitive, so entries such as ABC and abc are treated as equivalent for this lookup. If case-sensitive matching is essential, use a different formula approach rather than expecting VLOOKUP to distinguish them.

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.