Excel VLOOKUP Not Working? Fix the Common Causes

If Excel VLOOKUP is returning the wrong result or an error, first set its final argument to FALSE for an exact match, then confirm the lookup value is in the leftmost column of the search range and is stored in the same format as the value being searched. For example, with product IDs in A2:A5, product names in B2:B5, and the ID to find in D2, use =VLOOKUP(D2,$A$2:$B$5,2,FALSE); if D2 contains an ID in that range, the formula returns its matching product name.

Start with a quick VLOOKUP check

Before rebuilding a formula, inspect its four important parts: the value to find, the full table range, the return-column number, and the match setting. A small mistake in any one can produce a plausible but incorrect answer, not just an obvious error.

Is the value you want to find located in the first column of the selected range?
Does the column number point to the result column, counting from the range’s left edge?
Does the formula end in FALSE or 0 when you need an exact match?
Are both lookup values genuinely the same kind of value, such as text on both sides or numbers on both sides?
When copying the formula, does the table range stay in place?

Click the formula cell and inspect the formula bar. This helps catch an accidentally shifted range, a missing argument, or a formula that refers to a different lookup cell than expected. If the formula is displayed as text in the worksheet rather than calculated, check that the cell is not formatted as Text and that the formula begins with an equals sign.

How VLOOKUP works

VLOOKUP searches vertically down the first column of a table range. Once it finds a match, it returns a value from another column in the same row. Its column number is relative to the range, not the worksheet: if the selected range starts in column C, then C is column 1, D is column 2, and E is column 3.

The general syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The lookup value is what Excel searches for; table_array is the range to search; col_index_num identifies the column to return; and range_lookup selects exact or approximate matching. In Microsoft 365 for Windows, FALSE requests an exact match and TRUE requests an approximate match. If the last argument is omitted, Excel uses approximate matching, which is a frequent source of unexpected results.

VLOOKUP also has a structural limitation: the lookup column must be the first column in the chosen range. It can return values to the right of that column, but it cannot search one column and return a result from a column to its left. If that is what the worksheet requires, use a different lookup method or rearrange the data.

Fixing #N/A and missing matches

#N/A usually means that an exact match was not found. It does not always mean the value is visibly absent: the two entries may look alike while differing in spaces, data type, punctuation, or characters copied from another system. Check the value in the lookup cell and the corresponding source entry in the formula bar, not just how they appear on screen.

Look for leading or trailing spaces, including spaces introduced by copy and paste.
Check whether an ID such as 00427 is text in one place but the number 427 in another. A number format can display leading zeros without changing the underlying number.
Check for a different hyphen, apostrophe, or other character in codes and names.
Confirm that the search range actually includes the row containing the match.
For a text lookup, remember that VLOOKUP’s exact match is not case-sensitive. It treats text differing only in capitalization as a match.

Do not add wildcards as a general fix for #N/A. In an exact text lookup, an asterisk or question mark in the lookup value can act as a wildcard, but this changes what counts as a match and may return an unintended row. First establish why the values differ, then choose deliberately whether a partial match is appropriate.

Exact match versus approximate match

For product codes, employee IDs, names, and most ordinary lookup tables, use FALSE (or 0) as the final argument. It tells Excel to find the same value rather than a nearby one. With exact match, the first matching row is returned if the lookup column contains duplicates, so the order of duplicate entries can matter.

Approximate match is useful for ordered bands, such as assigning a category to a score based on thresholds. In that mode, the first column must be sorted in ascending order. Excel returns an exact match when available; otherwise, it returns the row for the largest first-column value that is less than the lookup value. If the column is unsorted, results can be unreliable. A formula that leaves off the final argument may therefore return a value that looks valid but belongs to the wrong band.

For example, if a threshold table lists lower bounds of 0, 60, and 80 in ascending order, approximate matching can map a score of 74 to the row beginning at 60. That is different from asking Excel to locate the exact score 74. Decide which behavior the table represents before choosing the match setting.

Check the return-column number and range

The col_index_num counts columns inside table_array, starting at 1. Suppose the range is B2:D20: a value in B is column 1, C is column 2, and D is column 3. Entering 4 requests a column outside the range and normally produces #REF!. Entering 1 returns the lookup-column value itself, which may be correct but is often a sign that the index was counted from the worksheet rather than from the selected range.

Make the range absolute when filling a formula down so it does not slide to the next rows. Dollar signs lock the range’s row and column references while allowing the lookup cell reference to change. In the example formula above, D2 can become D3 as the formula is copied, while $A$2:$B$5 remains the search table. You can also convert a data range into an Excel table and use structured references, which can expand as rows are added; check that the lookup column remains first in the VLOOKUP table_array.

For #REF!, inspect both the range and the column number. A deleted worksheet column can also invalidate a reference. For #VALUE!, check that the column number is a valid positive number and that the formula arguments have not been entered in the wrong order.

Resolve text, number, and spacing differences

Formatting and stored value are not the same thing. Applying a number format changes how a value looks, but it does not necessarily convert text digits into a numeric value. If Excel shows a warning indicator for numbers stored as text, its Convert to Number option can help. Alternatively, correct the source data consistently before running the lookup.

TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words. CLEAN removes certain nonprinting characters. Data copied from web pages or external systems can include a nonbreaking space, which TRIM alone does not remove. With the original text in A2, this helper formula replaces nonbreaking spaces, cleans certain nonprinting characters, and trims the result: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). Apply equivalent cleanup to the lookup value and the source lookup column; cleaning only one side may leave them unequal. Review cleaned data before replacing original records, since normalization can affect meaningful spacing or codes.

Leading zeros deserve special care. If 00427 is an identifier, it may need to remain text so the zeros are retained. Converting it to the number 427 changes the underlying value, even if a custom number format later displays zeros. Pick a consistent representation for identifiers rather than converting them blindly.

A practical troubleshooting sequence

Use this order to narrow down a problem without changing several things at once:

Set the match argument to FALSE if the task is to find the same ID, code, or label.
Verify that the lookup column is the first column in the table range.
Count the return column from the range’s left edge and confirm it is inside the range.
Check the value and source entry for text-versus-number differences, spaces, and unusual characters.
Lock the table range if copying the formula, then test the formula on a row whose expected result is known.
If a result still seems wrong, check for duplicate lookup values and determine which matching row is first.

Testing a known case is especially useful: if you know what a particular ID should return, use that value and compare the formula result with the source row. Once it works, test an ID near the top and bottom of the range as well. This can expose a range that accidentally omits some records.

When to use XLOOKUP instead

In Microsoft 365 and Excel 2021 or later, XLOOKUP can be easier to maintain when the return column is to the left of the lookup column or when you want separate lookup and return ranges. It uses exact match by default, so it avoids VLOOKUP’s omitted-argument approximate-match trap. With IDs in B2:B20, names in A2:A20, and the ID to find in D2, use =XLOOKUP(D2,B2:B20,A2:A20,"Not found"); it returns the corresponding name or the text Not found if there is no match.

VLOOKUP remains useful in older Excel files and when the lookup column is already at the left edge of a straightforward table. If you share workbooks with people using older Excel versions, confirm that their version supports any newer function you choose. XLOOKUP is not available in every older release, including Excel 2019 and Excel 2016.

Frequently Asked Questions

Why does VLOOKUP return the wrong row? The formula may be using approximate matching because its final argument is TRUE or omitted, or the first column may not be sorted for an approximate lookup. Use FALSE for an exact lookup. If the result is still unexpected, check for duplicate lookup values; VLOOKUP returns the first matching row.

Why does VLOOKUP show #N/A when the value is there? The visible entries may not be identical internally. Check for spaces, nonprinting characters, text-versus-number differences, and a range that stops before the matching row. Compare the actual formula-bar contents of both values.

Can VLOOKUP return a value from a column on the left? No. Its search column must be the first column of table_array, and it returns from a column to the right or from that first column. XLOOKUP can use separate lookup and return ranges and can return a value from the left when supported by the Excel version.

What does #REF! mean in VLOOKUP? It commonly means the return-column number is larger than the number of columns in the selected range, or that a referenced column was deleted. Count the return column from the range’s left edge and make sure the range still covers it.

Does VLOOKUP distinguish uppercase and lowercase? No. VLOOKUP’s text matching is not case-sensitive, so entries such as ABC and abc are treated as equal for this purpose. If capitalization itself 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.