Excel VLOOKUP Not Working? Fix the Cause

If VLOOKUP is not working in Excel, check that you are using the right match mode, that the lookup value and first table column have the same kind of data, and that the table range includes the return column. For a reliable exact match, include FALSE as the last argument; then use the error or wrong result to narrow down the cause.

How VLOOKUP works

VLOOKUP searches for a value in the leftmost column of a selected range, then returns a value from another column on the same row. Its arguments are the value to find, the table range, the number of the return column counted from the left edge of that range, and an optional match setting. That left-to-right design is important: VLOOKUP cannot look in a column to the right of the return column and then return a value from the left.

For example, suppose A2:C100 contains product IDs in column A, descriptions in B, and prices in C. E2 contains the ID to look up. With ID 1042 in E2 and 18.75 in the matching row of column C, this formula returns 18.75:

With IDs in A2:A100, descriptions in B2:B100, prices in C2:C100, and the ID to find in E2, use:

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

The 3 means “return the third column of the selected range,” not worksheet column C in every possible setup. If your table range starts in B, then its first column is B, its second is C, and so on. The dollar signs keep the range fixed if you fill the formula down.

Start with the match setting

A frequent source of surprising results is leaving out the fourth argument. If it is omitted, VLOOKUP uses approximate matching. Approximate matching is intended for ordered data, such as score bands or tax brackets; it can return the closest value that is less than or equal to the lookup value. The first column must be sorted in ascending order for that mode to give dependable results.

For ordinary lookups—finding a customer number, product code, or exact name—use FALSE for an exact match. Exact match does not require the first column to be sorted. TRUE explicitly requests approximate matching, while FALSE explicitly requests exact matching. Being explicit is safer than relying on the default, especially if someone later sorts the source data.

Approximate matching is not inherently broken when it returns a nearby result; it may be doing exactly what the formula requested. If you need an exact code but the formula uses TRUE or omits the final argument, change it to FALSE and confirm the result against the source row.

Fix #N/A: no exact match found

#N/A usually means an exact-match lookup did not find the value in the first column of the table range. Before changing the formula, compare the lookup value and its source entry carefully. A difference that is hard to see—such as an extra space, a different hyphen, or a number stored as text—can prevent a match.

Confirm the lookup value is actually present in the first column of the selected range, not merely somewhere in the table.
Check that the range starts at the lookup column. If the IDs are in B but the selected range begins in A, VLOOKUP searches A.
Compare data types. A numeric 1042 and text “1042” may look alike on the sheet but are not always equivalent in a lookup.
Look for leading or trailing spaces, nonprinting characters, or inconsistent punctuation in imported data.
Check for a misspelled key or a value that differs in one character. VLOOKUP exact matching is not case-sensitive, but it does require the characters to match.

To test whether text cleanup is needed, create a helper column beside the source data rather than altering the original records immediately. With a potentially messy key in A2, this formula removes many ordinary extra spaces and nonprinting characters; for example, an input with spaces around 1042 becomes 1042:

With the uncleaned lookup key in A2, enter:

=TRIM(CLEAN(A2))

TRIM and CLEAN do not remove every unusual character. Data copied from websites can contain a nonbreaking space, which may need separate treatment. With the original value in A2, this formula replaces the common nonbreaking-space character with a normal space before cleaning; “ 1042 ” then becomes “1042”:

With the source key in A2, enter:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Apply equivalent cleanup to both the lookup value and the source keys. Cleaning only one side can leave the mismatch in place. If keys are meant to be numbers, consider converting both sides consistently rather than relying on display formatting, which changes appearance but not necessarily the underlying value.

Understand other error messages

#REF! commonly points to an invalid return-column number. The third argument must be at least 1 and no greater than the number of columns in the selected table range. For example, if the range is A2:C100, the valid column numbers are 1, 2, and 3. A formula that asks for column 4 cannot return a value from that three-column range.

#VALUE! can appear when an argument is invalid, including a return-column number that is not usable. Check that the third argument is a positive whole-number column index and that the formula separators and parentheses are correct. Excel installations using a different regional setting may display semicolons instead of commas in formulas; use the separators Excel expects in that installation.

#NAME? can indicate that the function name or an argument has been entered incorrectly. Check the spelling of VLOOKUP and make sure text values in formulas are enclosed in straight quotation marks. If the formula displays as text instead of calculating, the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell to General, then re-enter the formula.

Correct a wrong result or a range that shifts

If VLOOKUP returns a value but it is not the one you expected, check the match setting first. Approximate matching on unsorted keys is a common explanation. With exact matching, look for duplicates: VLOOKUP returns the first matching row from the top of the selected range. If the same ID appears more than once, the result may be valid for the first occurrence but not the record you intended.

Next, verify the return-column number by counting from the left edge of the table range. A range such as B2:D100 has B as column 1, C as column 2, and D as column 3. Selecting the wrong starting column can make a correct-looking index return the wrong field.

When copying a formula down, a relative range can move. If the first formula refers to A2:C100, the next row may refer to A3:C101, excluding the first source row and including a new one. Lock the source range with dollar signs, as in $A$2:$C$100. Keep the lookup cell relative if each row should use its own lookup value.

To inspect a formula, select its cell and look at the formula bar. You can also go to Formulas > Evaluate Formula to step through how Excel evaluates it. On Excel for Mac, the same Evaluate Formula command is available from the Formulas tab, though ribbon layout can differ slightly by version. Checking the evaluated lookup value and table range often reveals an off-by-one column or an unintended blank.

Use wildcards and handle missing results carefully

For text lookups, VLOOKUP with FALSE can use wildcards: an asterisk matches any number of characters, a question mark matches one character, and a tilde escapes a wildcard when you need to search for the literal character. For instance, a search value containing “North*” can match a text entry beginning with North. Wildcards are useful for deliberately partial searches, but they can also match more than you intended; use a unique exact key when accuracy matters.

If a missing match is an expected possibility, you can show a readable message instead of #N/A. This does not fix a bad key or a broken range; it only changes how the missing-match case is displayed. With the same table layout as the earlier example, this returns the matching price when found and “Not found” when no exact match exists:

With IDs in A2:A100, prices in C2:C100, and the lookup ID in E2, use:

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

Prefer IFNA when the specific problem being handled is a missing match. A broad error handler can also hide issues such as a bad column index, making a formula appear successful when it needs repair.

When to use XLOOKUP instead

In Microsoft 365, XLOOKUP is often a clearer option for new formulas because it can search one range and return a value from a separate range, including a column to the left of the lookup column. It also uses exact matching by default. XLOOKUP is available in Microsoft 365 and Excel 2021; it is not available in some older perpetual versions of Excel.

For example, if IDs are in A2:A100, prices in C2:C100, and the ID to find is in E2, XLOOKUP can return the price without a numeric column index:

With IDs in A2:A100, prices in C2:C100, and the lookup ID in E2, use:

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

VLOOKUP remains useful when a workbook must work in older Excel versions, or when existing formulas already use it. It is also perfectly adequate for straightforward exact lookups once the range, match mode, and data are correct. Changing functions is not necessary just because a VLOOKUP returns an error; first diagnose whether the underlying key or range is wrong.

A practical troubleshooting order

Work from the simplest checks toward data cleanup. This makes it less likely that a formula gets rewritten to hide the real cause:

Read the result: #N/A suggests no match; #REF! points to a return-column or reference problem; a plausible but wrong value often indicates match mode, duplicates, or an incorrect column index.
Confirm the lookup column is the first column in the selected range and that the return column is inside that range.
Use FALSE for an exact match unless approximate matching is intentionally required.
Compare the lookup key with the source value, including data type, spaces, punctuation, and hidden characters.
Check duplicate keys and confirm which matching row VLOOKUP will return.
Lock the source range before filling the formula down, then test a known key whose answer you can verify directly.

For Excel for the web, the VLOOKUP syntax and core behavior are the same. On Mac, the function arguments and troubleshooting principles are also the same; ribbon placement and some interface details may vary. If a workbook is shared across platforms, using standard functions such as VLOOKUP and avoiding version-specific features can help preserve compatibility.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visible?
The displayed values may not be identical underneath. Check for text-versus-number differences, extra or nonprinting spaces, punctuation differences, and whether the lookup column is the first column of the table range. Also confirm the formula uses the intended exact-match setting.

Does VLOOKUP need the first column to be sorted?
Not when the final argument is FALSE for exact matching. If you use TRUE or omit that argument, VLOOKUP uses approximate matching and the first column should be sorted ascending for dependable results.

Why does VLOOKUP return the wrong column?
The column index is counted from the left edge of the range, not from worksheet column A. Recount the columns inside the selected range and make sure the range begins at the intended lookup column.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column of its range and returns a value from a column to its right. In Microsoft 365 or Excel 2021, XLOOKUP can search and return from separate ranges in either direction.

Why does VLOOKUP return the wrong duplicate?
VLOOKUP returns the first matching row from top to bottom. If keys are duplicated, check the first occurrence or make the lookup key unique by combining it with another field where appropriate.

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.