Microsoft Excel VLOOKUP Not Working? Fix Common Errors

If Microsoft Excel VLOOKUP is not working, first set its fourth argument to FALSE for an exact match, then confirm that the lookup value is in the leftmost column of the selected range. If the formula still fails, check for mismatched data types, extra spaces, an incorrect column number, or a range that shifted when you copied the formula.

Start with a reliable exact-match formula

VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. Its syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The last argument controls the match type: FALSE requests an exact match, while TRUE requests an approximate match. Because Excel uses approximate matching when that argument is omitted, specifying FALSE prevents many unexpected results.

For example, suppose A2:A4 contains product IDs, B2:B4 contains product names, and C2:C4 contains prices. The rows are P-104, Travel mug, 12.50; P-105, Notebook, 8.75; and P-106, Desk lamp, 19.00. E2 contains P-105. With that layout, this formula returns 8.75:

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

The 3 means return a value from the third column of the selected range, not necessarily column C of the worksheet. The dollar signs lock the range so it does not move if you copy the formula to another row. If the formula returns an unexpected value or an error, the message and the formula’s structure usually point toward the cause.

Understand the error you see

#N/A means VLOOKUP did not find a match under the requested match rules. It is commonly caused by a missing key, a text-versus-number mismatch, extra spaces, or searching in the wrong column. It can also be the intended result when no matching record exists.

#REF! commonly indicates that the column number asks for a column outside the selected range. For example, a range with two columns cannot return column 3. #VALUE! can appear when the column index is less than 1. Check the range’s width and the index argument before changing the source data.

A result that is valid but belongs to the wrong record often points to approximate matching, duplicate keys, or a range that starts in the wrong place. A formula displayed as text rather than calculated may have been entered into a cell formatted as Text, or the formula may begin with an apostrophe. A result that does not update may indicate manual calculation mode. These are different problems, so identify the symptom before editing the formula.

Check the lookup column and return-column number

VLOOKUP can only search the leftmost column of its table_array. If product IDs are in column B but the selected range begins at A, VLOOKUP searches A instead. Select a range that begins with the key column, such as B2:D100, and count the return column from that left edge. In that example, the first column is B, the second is C, and the third is D.

Also verify that the index is within the range. If the range is B2:D100, the largest valid column index is 3. Inserting a worksheet column outside the lookup range usually does not change which columns the formula searches, but inserting or rearranging columns within the range can affect what its index points to. Review the range and the returned field after structural edits.

VLOOKUP cannot return a value from a column to the left of the lookup column. If the key is in C and the required result is in A, rearrange a copy of the data or use a lookup method that allows separate lookup and return ranges. This limitation is about the formula’s design, not a setting that can be fixed by changing the column index.

Make sure the values really match

Two cells can look identical while holding different values. A numeric ID such as 104 may be stored as a number in one table and as text in another. Number formatting does not convert the stored value: formatting a number to display leading zeros, for example, does not necessarily make it equivalent to a text key that includes those zeros. Check both source columns and standardize the underlying values before relying on the lookup.

Dates can cause a similar puzzle. Excel stores dates as serial numbers, while a cell’s date format controls how those numbers appear. A date imported as text may look like a genuine Excel date but fail to match it. Compare the source values and, where needed, convert the imported column into real dates or consistently stored text. Do not assume that matching appearance proves matching data.

For a quick check, inspect the cells directly and compare their formats and contents. You can also test whether a suspected number is stored as text by seeing whether it behaves like a number in a simple calculation. When correcting a whole column, use a conversion method appropriate to the data and verify a few rows before filling or replacing values. Avoid changing IDs blindly: leading zeros may be meaningful, such as in postal codes or account references.

Remove spaces and invisible characters

Imported data may contain leading or trailing spaces, repeated spaces, line breaks, or nonbreaking spaces. These characters can make an apparently identical key fail an exact lookup. A helper column is often safer than editing the original data because it preserves the imported value for comparison and lets you see what was changed.

If A2 contains the text with ordinary spaces “ P-105 ”, and B2 is a helper cell for the cleaned version, this formula returns P-105:

=TRIM(A2)

TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to single spaces. It does not remove every possible invisible character. CLEAN can remove many nonprinting characters, but it is not a universal cleaner for all Unicode characters; nonbreaking spaces often require a separate replacement step. If TRIM does not resolve the issue, inspect the source or test a carefully chosen cleanup method on a copy of the data. Clean both the lookup value and the table’s key column in the same way, then point VLOOKUP at the cleaned keys.

Check approximate matching, duplicates, and wildcards

With FALSE, VLOOKUP seeks an exact match. With TRUE, or when the fourth argument is left out, it uses approximate matching. In that mode, the first column should be sorted in ascending order for a dependable result; the formula returns the closest value that does not exceed the lookup value. An unsorted list can therefore produce a result that looks plausible but is wrong. For IDs, names, and other keys that should match exactly, use FALSE explicitly.

If the lookup column contains the same key more than once, VLOOKUP returns the first matching row it encounters. It does not report that the key is duplicated or automatically select the newest, largest, or otherwise preferred record. Check for duplicate keys if the result seems inconsistent, and decide which record should be returned before changing the formula.

Exact-match mode also supports wildcard characters in text lookups: an asterisk represents any sequence of characters, and a question mark represents one character. A tilde can escape a wildcard when you need to search for that character literally. This can be useful for intentional partial matching, but it can also cause surprising results if a key contains wildcard characters and you expect a literal exact comparison.

Fix references and calculation issues

When filling a formula down, make sure the lookup cell changes by row while the table range stays fixed. In the example formula, E2 is relative, so a copied formula can become E3; $A$2:$C$4 is absolute, so the lookup range remains in place. In Excel for Microsoft 365 on Windows, select a cell reference in the formula bar and press F4 to cycle through reference styles. On a Mac, the equivalent shortcut may depend on keyboard settings; use the formula bar to add or remove dollar signs if the function key behaves differently.

Confirm that the formula is being calculated. Select Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows if the workbook is set to Manual. If a formula appears literally in the cell, change the cell’s number format from Text to General, then re-enter the formula; simply changing the format may not make an already-entered text string calculate. Check for a leading apostrophe and for Show Formulas being enabled if formulas appear across the worksheet instead of their results. The Excel formula itself works the same on Mac, though ribbon placement can vary by version.

Use a practical troubleshooting sequence

Work from the simplest checks to the less obvious ones. This helps avoid modifying source data when the real issue is only a misplaced range or omitted match argument.

Read the displayed result or error and check whether the formula is calculating at all.
Confirm that the lookup value is in the first column of the selected range.
Use FALSE for an exact match, and count the return column from the left edge of the range.
Check that the lookup and source keys have the same underlying type and do not contain unwanted spaces or characters.
Lock the lookup range before copying the formula, and check for duplicate keys if the returned row is unexpected.
If the result is stale, check calculation mode; if the result is wrong only for some rows, compare those specific keys with their source values.

For a quick test, temporarily look up a key you can see in the source table and verify the returned value by locating its row manually. This separates a formula-structure problem from missing or inconsistent source data. Once it works, restore the intended lookup value and test a few boundary cases, such as the first and last rows in the range.

When XLOOKUP is a better fit

In Excel for Microsoft 365, XLOOKUP is an alternative when VLOOKUP’s leftmost-column rule or numbered return column is inconvenient. It uses separate lookup and return ranges, so those ranges can be in either order, and it uses exact matching by default. For the product example above, with E2 containing P-105, IDs in A2:A4, and prices in C2:C4, this formula returns 8.75:

=XLOOKUP(E2,A2:A4,C2:C4)

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it may not work in older Excel releases or in files opened by users with those versions. VLOOKUP remains useful for compatibility and for existing workbooks. If you keep VLOOKUP, an exact-match argument, a correctly anchored range, and clean, consistent keys address most everyday failures.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value is there?
Check whether the key is stored as text in one place and a number in the other, or whether either value contains extra spaces or invisible characters. Also confirm that the lookup column is the first column in the selected range and that the value actually matches the stored key.

Why is VLOOKUP returning the wrong row?
The formula may be using approximate matching because its fourth argument is TRUE or omitted. Use FALSE for an exact match. If the result is still unexpected, check for duplicate keys; VLOOKUP returns the first matching row.

Does VLOOKUP search to the left?
No. It searches the first column of its selected range and returns a value from a column to the right. Use XLOOKUP in supported Excel versions, or reorganize the lookup data, when the return value is to the left of the key.

Why does my VLOOKUP formula show as text?
The cell may be formatted as Text, the formula may begin with an apostrophe, or Show Formulas may be on. Set the cell to General, re-enter the formula, and check the worksheet’s formula display setting.

Can VLOOKUP return more than one matching result?
VLOOKUP returns a value from the first matching row; it is not designed to list every match. If you need multiple records, use a method designed to return multiple results, such as FILTER in Microsoft 365 or Excel 2021 and later, or filter the source table directly.

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.