VLOOKUP Excel Not Working? Fix the Most Common Errors

If VLOOKUP in Excel is not working, first set its fourth argument to FALSE for an exact match, then check that the lookup value is in the leftmost column of the selected range and that both values use the same data type. Those checks resolve many lookup failures; the error message or unexpected result usually points to the next thing to inspect.

How VLOOKUP works

VLOOKUP searches down the first, or leftmost, column of a range for a value. It then returns a value from another column on the same row. Its arguments are the value to find, the full range to search, the number of the return column counted from the range’s left edge, and an optional match setting. A common source of trouble is treating the return-column number as a worksheet column letter: it is a position inside the selected range, not the sheet.

For example, with product IDs in A2:A100, product names in B2:B100, regions in C2:C100, and the ID to look up in E2, the following searches column A and returns the region from the third column of the range. If E2 contains 102 and row 3 has West in C3, the result is West.

With that layout, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE).

The dollar signs keep the data range fixed if you copy the formula to other rows. Without them, Excel may shift the range, causing later lookups to search the wrong cells.

Check these basics first

Before changing data or rebuilding a formula, verify the structure and settings. In Microsoft Excel for Microsoft 365 on Windows, click the formula cell and inspect the formula bar; the displayed result alone may not reveal a shifted range or an omitted argument.

Use an exact match. Enter FALSE as the fourth argument. The equivalent value 0 also means exact match, but FALSE is easier to read.
Put the lookup column first. VLOOKUP cannot search one column and return a value from a column to its left.
Count the return column from the range’s left edge. In A:C, A is 1, B is 2, and C is 3.
Check the references. Confirm that the lookup cell and the table range include the intended rows and columns.
Compare the values themselves. A value that looks identical can differ because it contains spaces, is stored as text rather than a number, or includes an unseen character.
Look for repeated keys. VLOOKUP returns the first matching row, not every matching row or necessarily the most recent one.

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

#N/A means Excel could not find a match under the formula’s current rules. In an exact-match lookup, start by copying the lookup value and comparing it with the source key. Differences that are hard to see include a trailing space, a leading space, a hyphen in one value but not another, or a number stored as text in one location and as a numeric value in the other.

If the lookup cell has ordinary extra spaces and the source IDs are clean, TRIM can remove leading and trailing spaces from the lookup value. Suppose E2 contains the text value 102 , while A2:A100 contains clean IDs and C2:C100 contains regions; the formula below returns West if ID 102 is on the West row.

With IDs in A2:A100, regions in C2:C100, and the padded lookup value in E2, use =VLOOKUP(TRIM(E2),$A$2:$C$100,3,FALSE).

TRIM does not clean the source column, and it does not remove every possible invisible character. In particular, data copied from web pages may contain a nonbreaking space, which looks like an ordinary space but is a different character. If the source keys are dirty, clean a helper column as well or use a suitable data-cleaning step before looking them up. Also check whether a value has leading zeros: the text ID 00102 is not the same key as the number 102 if those zeros are meaningful.

For a number-versus-text mismatch, make the lookup value and source keys the same type. If E2 contains the text digits 102 and the IDs in A2:A100 are numbers, VALUE converts that text to a number. With regions in C2:C100, the formula returns West when numeric ID 102 is in the table.

With numeric IDs in A2:A100, regions in C2:C100, and text digits in E2, use =VLOOKUP(VALUE(E2),$A$2:$C$100,3,FALSE).

Do not convert identifiers blindly. A code such as 00102 may need to remain text, and a long account number can lose meaning if treated as a number. Choose a consistent representation that preserves the identifier, then apply it to both the lookup value and the table.

Correct a match that returns the wrong row

The most common reason for a plausible but incorrect result is approximate matching. If the fourth argument is omitted or set to TRUE, VLOOKUP uses approximate match. The first column must then be sorted in ascending order for dependable results. Excel returns an exact match when it finds one; otherwise, it can return the row for the largest value less than the lookup value. An unsorted list can therefore produce a result that looks valid but is not the intended one.

For example, with IDs 100, 200, and 300 in ascending order in A2:A4 and their regions in C2:C4, a lookup value of 250 with TRUE returns the region on the 200 row. That is correct approximate-match behavior, but it is often wrong for a product code or customer ID. For ordinary record lookups, use FALSE instead of relying on sorting.

Another cause is a duplicate key. VLOOKUP searches from top to bottom and returns the first match it encounters. If customer ID 102 appears twice, the formula does not know which record is more current or preferred. Sort or filter the source data according to a clear rule, remove unintended duplicates, or use a method designed to return multiple matches if that is what the task requires.

Finally, confirm the return-column index. A formula can find the right key but return the wrong field if the number is off by one or the range begins in a different column than expected. Count from the first column of the range each time, especially after inserting columns or editing the formula.

Understand #REF!, #VALUE!, and #NAME? errors

#REF! usually means the requested return column is outside the selected range, or a referenced cell or column was deleted. For example, if the range is A2:C100, it contains three columns; asking for column index 4 cannot return a value. With IDs in A2:A100, names in B2:B100, and regions in C2:C100, this formula returns #REF! because its requested fourth column is not present.

With the table in A2:C100 and the lookup value in E2, =VLOOKUP(E2,$A$2:$C$100,4,FALSE) produces #REF!; use 3 to return the region in column C.

#VALUE! can indicate an invalid column index, such as zero or a negative number. It can also appear when an argument is not a usable value. Check the formula’s commas, parentheses, and index before assuming the source data is at fault. #NAME? often points to a misspelled function name or text that should be enclosed in straight double quotation marks. Excel function names and formula separators can be affected by language and regional settings, so a formula copied from another locale may need adjustment.

If a formula that used to work now shows #REF!, inspect the formula after deleting or moving columns. A fixed number such as 3 still means the third column in the specified range; it does not automatically mean the worksheet column C if the range changes.

Check formula references and calculation settings

When you fill a lookup formula down a column, the lookup-cell reference should usually change by row while the source table stays fixed. Dollar signs make a reference absolute. If the table range was not fixed, copying a formula from row 2 to row 3 could shift A2:C100 to A3:C101, omitting a key at the top and including an unintended row at the bottom.

Also check whether Excel is calculating automatically. On Windows, open Formulas > Calculation Options and select Automatic if the workbook is set to Manual. A manual-calculation workbook can display an old result after values change; recalculate the workbook after correcting the setting. On Mac, Calculation Options is also on the Formulas tab, though the ribbon layout may differ. Excel for the web recalculates supported formulas, but menus and the editing interface may not match the desktop app exactly.

A related misconception is that a formula is broken simply because the cell displays the formula text. If the cell is formatted as Text, or the formula was entered with a leading apostrophe, Excel may treat it as text rather than calculate it. Change the cell format to General, then edit and re-enter the formula. Also make sure Show Formulas is not enabled; it changes the worksheet display without changing the lookup itself.

When to use another lookup method

VLOOKUP is useful when the key is in the leftmost column and the return field is to its right. If the value to return is to the left of the key, rearranging a well-structured table may be simplest. In Microsoft 365 and Excel 2021 or later, XLOOKUP can search in either direction and has a separate not-found result argument. It is not available in older perpetual Excel versions, so a workbook using it may not work for everyone who opens the file.

INDEX and MATCH are another option in older Excel versions when the lookup and return columns are in either order. They require a more involved formula, so VLOOKUP can remain easier to maintain for straightforward tables. Pick a method based on your Excel version, the direction of the lookup, whether you need multiple matches, and who must open the workbook. Changing functions will not fix dirty or inconsistent source data; those problems need to be addressed regardless of the lookup method.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though I can see the value?
Check for spaces or hidden characters, text-versus-number differences, and punctuation differences. Confirm that the lookup column is the first column in the range and that the formula is using the match behavior you intend. A visible match is not necessarily an exact underlying match.

Why does VLOOKUP return the wrong result?
Check whether the fourth argument is TRUE or omitted, which enables approximate matching. For an exact record lookup, set it to FALSE. If the key occurs more than once, VLOOKUP returns the first matching row, so inspect duplicates too.

Can VLOOKUP return a value from a column on the left?
No. The lookup column must be the first column of the selected range, and the return column must be to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or use INDEX and MATCH in versions that do not include XLOOKUP.

What does the number in VLOOKUP mean?
It is the return-column position counted from the left edge of the table range. If the range is B:D, B is 1, C is 2, and D is 3; it is not the worksheet’s column number.

How can I show a message instead of #N/A?
IFNA can replace a not-found error with readable text. With IDs in A2:A100, regions in C2:C100, and the lookup value in E2, the formula below returns the matching region or Not found when no exact key exists.

With that layout, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found").

This handles #N/A only; it does not hide other errors such as #REF!, which should be diagnosed rather than masked.

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.