VLOOKUP Not Working in Excel? Fix the Common Causes

If VLOOKUP returns an error or the wrong result, first check that the lookup value is in the leftmost column of the selected range and use an exact match. With the value to find in E2, the lookup table in A2:C100, and the answer in its third column, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE). Then check for mismatched data, an incorrect column number, or a range that shifted when the formula was copied.

How VLOOKUP works

VLOOKUP searches vertically down the first column of a range, finds a match, and returns a value from another column in the same row. Its four arguments are the value to find, the table range, the number of the return column counted from the range’s left edge, and whether to find an exact or approximate match. In the opening example, Excel searches column A and returns the corresponding value from column C.

The last argument matters greatly. FALSE requests an exact match; TRUE, or leaving that argument out, requests an approximate match. For ordinary lookups such as matching an employee ID, product code, or customer name, use FALSE. An approximate lookup is intended for ordered ranges such as tax brackets or grading thresholds, and the first column must be sorted in ascending order for reliable results.

VLOOKUP only searches the first column of its selected range and returns values to its right. It does not search across a row, nor can it return a value from a column to the left of the lookup column. Those limits explain many formulas that look plausible but cannot produce the desired answer.

Start by identifying the symptom

The error or behavior usually narrows down the cause. A lookup that returns #N/A has not found a match under the formula’s current rules. #REF! often means the requested return-column number is outside the selected range, while #VALUE! can indicate an invalid argument, including a column number below 1. A plausible but incorrect result often points to approximate matching, duplicate keys, or a wrong return-column number.

If Excel displays the formula text instead of calculating it, check whether the cell is formatted as Text or whether Show Formulas is enabled. If the formula appears correct but the answer does not update after source data changes, check calculation settings. Treat these as separate issues: changing the lookup logic will not fix a cell that Excel is displaying as text.

Fix #N/A and values that look identical

With an exact match, Excel must find the same underlying value. Two entries can look identical on screen but differ because one is stored as text and the other as a number. For example, the number 104 and the text value “104” are not necessarily interchangeable in a lookup. This is common when one list was imported from a CSV file or copied from another system. Check the cell’s contents and number format, and use a consistent data type on both sides rather than changing formats blindly.

Spaces can also prevent a match. A trailing space after a product code, a leading space before a name, or a nonbreaking space copied from a web page may be difficult to see. Compare the source values in the formula bar, or test a suspicious cell with a length check. TRIM removes ordinary extra spaces at the beginning and end of text, but it does not remove every unusual whitespace character. Cleaning imported data may therefore require replacing the specific character or re-importing the source correctly.

Other mismatches include punctuation, different spellings, hidden characters, and dates stored as text rather than as Excel date values. A date shown as 3/5/2025 may be text in one list and a real date serial in another. Standardize the underlying values before relying on the lookup. Avoid adding wildcards as a quick fix unless partial matching is genuinely intended: with an exact-match lookup, a text lookup value can use * for any sequence of characters or ? for one character, but wildcard matches can select an unintended record.

If the value truly may not exist, it is reasonable to show a clear message instead of an error. With the lookup value in E2 and a three-column table in A2:C100, this formula returns “Not found” only when VLOOKUP returns #N/A: =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). Fix the cause before wrapping errors, though; otherwise, a broken range or wrong formula can be hidden behind a reassuring message.

Check the range and return-column number

The table range must include both the lookup column and the column containing the answer. The return-column number is counted from the left side of that range, not from the worksheet’s column letters. If the selected range is B2:D100, then B is column 1, C is column 2, and D is column 3. A formula using 3 returns values from D, not worksheet column C.

Also confirm the lookup column is first in the range. If the IDs are in column C and the names to return are in column A, selecting A:C does not make VLOOKUP search column C; it searches column A. You could rearrange the source table or use a lookup function that supports searching in either direction.

When copying a formula down, lock a fixed table range with dollar signs so it does not move. For example, $A$2:$C$100 remains fixed as the formula is filled into lower rows. Without those absolute references, a relative range such as A2:C100 can shift to A3:C101 in the next row, potentially excluding the first record. Keep the lookup cell relative if each row should use its own value.

A #REF! result can appear if the column index asks for a column beyond the width of the table range, or if a referenced column was deleted. Count the columns in the selected range again and make sure the return-column number is at least 1 and no greater than the number of columns in that range.

Why VLOOKUP can return the wrong answer

One common cause is an omitted or incorrect final argument. If the formula uses TRUE or leaves that argument blank, Excel may return the nearest qualifying value rather than an exact one. This can appear to work on some rows and fail on others, particularly when the first column is unsorted. For IDs, names, and codes, specify FALSE explicitly.

Another cause is a duplicate lookup value. VLOOKUP returns the first matching row it encounters, not every row with that key. If customer ID 104 appears more than once, the returned value comes from the first matching record in the selected range. Decide whether duplicates are valid, and if not, remove or resolve them in the source data. If they are valid, define which record should be selected instead of assuming VLOOKUP will choose the newest or most relevant one.

Finally, verify the return-column number by tracing one known record manually. A formula can find the right row while returning the wrong field—for instance, a department instead of a salary—because the index is off by one. Make sure the chosen return column represents the information you actually need.

A practical troubleshooting sequence

Work through these checks in order, testing the formula after each relevant correction. Changing one thing at a time makes it easier to identify the cause instead of masking it.

Read the exact error or inspect whether the displayed result is simply the wrong field.
Confirm that the first column of the selected range contains the values being searched for.
Set the last argument to FALSE for an exact match.
Check the lookup value and source key for matching data types, spaces, spelling, and hidden characters.
Count the table columns from the range’s left edge and verify the return-column number.
Use absolute references for a fixed table range before filling the formula down.
Check for duplicate keys and verify the first matching row is the intended one.
If Excel shows formula text or stale results, check cell formatting, Show Formulas, and calculation settings.

On Windows, press Ctrl+` to toggle the display of formulas; on a Mac, Control+` is the corresponding shortcut on many keyboards, though the key layout can vary. You can also use Formulas > Show Formulas in the ribbon. If a formula was entered while its cell was formatted as Text, change the format to General, then edit and re-enter the formula so Excel evaluates it. Merely changing the format may not convert an already-entered text string into a working formula.

When to use XLOOKUP instead

XLOOKUP is often a more flexible alternative in Microsoft 365 because it can search one range and return a value from a separate range, including a column to the left. It also uses exact matching by default, and its optional not-found argument can provide a message without wrapping the whole function in IFNA. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in some older Excel releases, so consider who needs to open the workbook before replacing a widely compatible formula.

With IDs in A2:A100, names in B2:B100, and the requested ID in E2, this exact-match formula returns the name or “Not found”: =XLOOKUP(E2,A2:A100,B2:B100,"Not found",0). It can return a value from a range to the left as well as to the right. For a workbook shared with people using older versions of Excel, VLOOKUP or INDEX and MATCH may be safer choices for compatibility.

VLOOKUP remains useful when the lookup key is the first field in a compact table and you want a formula supported across many Excel versions. Switching functions is not necessary if the existing formula is correct; fixing the range, match mode, or data mismatch is usually the more direct solution.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is in the table?
It may not be an exact underlying match. Check for text-versus-number differences, extra spaces, hidden characters, or a lookup range whose first column is not the key column. Also confirm that the formula uses the intended table range and exact-match setting.

Why does VLOOKUP return the wrong row?
Check whether the last argument is TRUE or omitted, which allows approximate matching. Use FALSE for exact matching of IDs and labels. If the key appears more than once, VLOOKUP returns the first matching row in the range, so duplicate records may explain the result.

Can VLOOKUP search to the left?
No. It searches the leftmost column of its table range and returns a value from a column to the right. Reorder the source data, or use XLOOKUP in Microsoft 365 or Excel 2021 and later, which can return values from either side.

Why does my VLOOKUP formula show instead of calculating?
The cell may be formatted as Text, the formula may have been entered with a leading apostrophe, or Show Formulas may be on. Turn off Show Formulas, set the cell to General, and re-enter the formula if it was stored as text.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s ordinary text matching is not case-sensitive, so values such as “abc” and “ABC” are treated as matches. If capitalization must distinguish records, 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.