VLOOKUP Not Working? Common Excel Causes and Fixes

VLOOKUP usually fails because the lookup value and the first column of the search range do not match exactly, the formula uses the wrong range or column number, or Excel is using approximate matching. With IDs in A2:A4, regions in C2:C4, and the lookup ID 102 in E2, =VLOOKUP(E2,$A$2:$C$4,3,FALSE) returns East. The checks below help identify which issue is affecting your workbook.

How VLOOKUP works

VLOOKUP searches vertically for a value in the leftmost column of a range, then returns a value from another column in the same row. Its arguments are the value to find, the range to search, the position of the return column within that range, and whether to use approximate or exact matching.

In the example, the search range starts in column A. Column A is position 1, B is position 2, and C is position 3 within that range. The number 3 therefore tells Excel to return the value from column C. It does not mean worksheet column C in every formula: if the range started in B, then worksheet column D would be position 3.

The final argument matters. FALSE requests an exact match and is generally the safest choice for names, product codes, invoice numbers, and other identifiers. TRUE, or leaving the argument out, allows approximate matching. Approximate matching assumes the first column is sorted in ascending order; if it is not, results may be wrong even when the formula produces an answer.

Start with these quick checks

Before changing data or rebuilding a formula, inspect its structure. Click the cell and read the formula in the formula bar. Check the lookup cell, the first column of the range, the column number, and the final match argument. Then verify that the value you expect to find is actually present in the range.

Use FALSE as the fourth argument when you need an exact match.
Confirm that the lookup column is the leftmost column in the selected range.
Count the return column from the left edge of the range, not from the worksheet’s A column.
Check that the range includes every relevant row and the column you want returned.
Look for hidden spaces, different data types, and duplicate lookup values.

For example, if the formula searches A2:C100, the third argument returns column C. If you change the range to B2:C100 but leave the third argument unchanged, the requested third column is outside the range and VLOOKUP can return a reference error.

Fixing #N/A when a value seems to be there

#N/A means VLOOKUP did not find a match under the rules in the formula. A frequent cause is a difference that is hard to see: a trailing space, a leading space, a nonprinting character, or a value stored as text on one side and as a number on the other. The cells may look identical on screen but still be different to Excel.

Compare the source and lookup cells directly. Check the formula bar, widen the column, and inspect whether one value is left-aligned while the other is right-aligned; alignment can be a clue, though formatting can change it. A number displayed as 00123 may be stored as text, while another cell contains the number 123. Those are not the same exact-match value, and numeric formatting alone does not make them equivalent.

Spaces are another common culprit. TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words, but it does not remove every possible invisible character. CLEAN can remove some nonprinting characters. If the source data needs regular cleanup, add a helper column, clean the values there, and point VLOOKUP at the cleaned column. Nonbreaking spaces copied from websites may need to be replaced separately.

Also check that the lookup value is in the first column of the selected range. VLOOKUP cannot search a column to the right and return a value from a column to its left. If the value appears somewhere else in the table but not in the range’s first column, an exact-match formula still returns #N/A.

When VLOOKUP returns the wrong result

If a formula returns a plausible but incorrect value, check whether approximate matching is being used. When the fourth argument is omitted, VLOOKUP uses approximate matching. For example, searching a list of unsorted product codes with that default can return a nearby row instead of the exact code. Add FALSE explicitly for identifiers and other exact-match lookups.

Approximate matching is useful in cases such as assigning a commission rate from ascending threshold values, but the first column must be sorted from smallest to largest for the result to be dependable. If your thresholds are not sorted, sort them or use a suitable exact-match or other lookup approach instead. Do not treat an answer that looks reasonable as proof that the formula found the intended row.

Duplicates can also explain an unexpected answer. VLOOKUP returns the first matching row it encounters from the top of the search range. If an ID occurs multiple times, it will not automatically return the newest, last, or otherwise preferred record. Decide which duplicate should be authoritative, remove or resolve duplicates if appropriate, or use a method designed to return a different matching row.

Finally, verify the column index. The formula may be finding the correct row but returning a neighboring field because its column number is off by one. Count from the first column of the range, and check that the returned value belongs to the record you intended.

Repairing #REF!, #VALUE!, and formulas that display as text

#REF! commonly signals that the requested return column is outside the selected range, or that a referenced row or column was deleted. If the range has three columns, a column index of 4 cannot return a value from it. Expand the range or correct the index. A column index of zero or a negative number is also invalid; the return-column number must be at least 1.

#VALUE! can occur when an argument has an unsuitable value, including an invalid column index. Check that the third argument is a positive number and that the arguments are separated correctly. The examples here use commas. Depending on regional settings, Excel may expect semicolons as formula separators; if Excel rejects a pasted formula, use the separator your installation accepts.

If the cell shows the formula itself instead of its result, make sure the cell is not formatted as Text. Change the format to General, then edit the cell and press Enter so Excel interprets the entry as a formula. A leading apostrophe can also make an entry display as text. To check whether formula display is enabled, use Formulas > Show Formulas in Excel for Microsoft 365 on Windows. The Windows shortcut is Ctrl+`; shortcut behavior can differ on Mac, so use the ribbon command if needed.

When a formula appears correct but the result does not update after source data changes, check Formulas > Calculation Options > Automatic. If calculation is set to Manual, Excel may not recalculate every formula automatically. This setting applies to the workbook session and can affect more than just the VLOOKUP cell.

Keep references and ranges from shifting

When you copy a formula down, a relative range such as A2:C100 can shift to A3:C101. That may be intentional for some references, but it is usually not what you want for a fixed lookup table. Dollar signs lock the range so it stays in place when copied. In the opening example, the lookup cell E2 remains relative while the table range is fixed.

A fixed range still has a boundary. If new records are added below row 100, a formula searching only A2:C100 will not see them. You can extend the range or convert the data to an Excel table using Insert > Table. Tables expand as rows are added, which can make lookup sources easier to maintain. Review the formula after changing the data structure, especially if columns are inserted or renamed.

It is also worth checking for blank rows or accidental gaps in the source. A blank row does not automatically invalidate an exact-match search, but a range that stops before the record does. Select the intended range carefully, and avoid selecting only the visible portion of a table when additional records sit below it.

When another lookup method is a better fit

VLOOKUP remains useful for straightforward searches where the lookup field is the first column of the range and the return field is to its right. If the layout changes often, however, a fixed column number can become fragile: inserting a column inside the range may change which field that number refers to.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can look in one column and return a result from another, including a column to the left, and exact match is its default behavior. With IDs in A2:A4, regions in C2:C4, and the lookup ID in E2, =XLOOKUP(E2,A2:A4,C2:C4) returns East. This separates the search and return ranges, so the formula does not need a numeric column index.

For older Excel versions, INDEX and MATCH can offer more layout flexibility than VLOOKUP, though the formula is more complex. Choosing a different function does not solve mismatched data types, invisible spaces, or duplicate records by itself. First confirm that the data and the intended match rule are sound, then choose the lookup function that best suits the workbook and the Excel version people will use.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value is visible?
Often the two values differ in a way that is not obvious, such as a space, a nonprinting character, or text on one side and a number on the other. Also confirm that the value is in the first column of the selected range and that the formula uses the intended match type.

Why does VLOOKUP return the wrong row?
Check whether the fourth argument is missing or TRUE. That requests approximate matching, which depends on an ascending-sorted first column. Use FALSE for an exact match. If duplicate keys exist, VLOOKUP returns the first matching row in the range.

Can VLOOKUP return a value from a column to the left?
No. The lookup field must be in the range’s leftmost column, and VLOOKUP returns from a column to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or consider INDEX and MATCH in versions where XLOOKUP is unavailable.

Why does VLOOKUP show the formula instead of a result?
The cell may be formatted as Text, the entry may begin with an apostrophe, or Show Formulas may be switched on. Change a text-formatted cell to General and re-enter the formula, and check Formulas > Show Formulas.

Does VLOOKUP distinguish uppercase and lowercase letters?
No. VLOOKUP’s text matching is not case-sensitive, so values that differ only by capitalization are treated as matches. If capitalization must distinguish records, use a case-sensitive approach rather than relying on VLOOKUP alone.

A reliable troubleshooting order

Start by confirming the match rule, then inspect the lookup value and the first column for data-type differences or hidden characters. Next, verify the range boundaries and count the return column from the range’s left edge. If the formula still fails, check duplicates, calculation settings, and whether the cell is treating the entry as text. This sequence separates problems in the data from problems in the formula and usually reveals why a VLOOKUP is not working without rebuilding the entire worksheet.

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.