VLOOKUP Not Working? Fix Common Excel Lookup Errors

If VLOOKUP is not working, first check that the lookup value is in the leftmost column of the selected range and use FALSE as the fourth argument for an exact match. Then check for mismatched data types, extra spaces, an incorrect column number, or a range that moved when you copied the formula.

How VLOOKUP works—and where it can fail

VLOOKUP searches down the first column of a table for a value, then returns a value from another column in the same row. Its arguments are the value to find, the table range, the number of the return column counted from the range’s left edge, and an optional match setting. This design makes the lookup range and the final argument especially important.

For example, if product codes are in column A and prices are in column C, a lookup table spanning A:C has three columns. The return-column number for the price is therefore 3, not 2: VLOOKUP counts columns inside the selected range, not worksheet column letters. It also searches only the first column of that range, so it cannot look to the left to return a value from a column before the lookup column.

In Microsoft 365 for Windows, enter or edit the formula in the formula bar or a worksheet cell. Excel for Mac uses the same VLOOKUP arguments and formula syntax; some ribbon and keyboard details differ, but the main causes and fixes are the same.

Start with the match setting

The fourth argument controls whether Excel seeks an exact or approximate match. If it is left out, VLOOKUP uses approximate matching. That default is a frequent reason a formula returns an unexpected result: a close value may be selected even though the lookup value does not appear in the table.

For product IDs, names, invoice numbers, and other keys that should match exactly, include FALSE (or 0) as the fourth argument. With product codes in A2:A100, prices in C2:C100, and the code to find in E2, use this formula:

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

If E2 contains SKU-104, A10 contains SKU-104, and C10 contains 18.5, the formula returns 18.5. The dollar signs keep the table range fixed if you fill the formula down. Without the final FALSE, Excel may use approximate matching, which is intended for ordered bands such as tax or grading thresholds and requires the first table column to be sorted in ascending order for reliable results.

Approximate matching is not simply a faster version of exact matching. Use it only when the table represents ranges and you understand the threshold behavior. For a list of unique identifiers, exact match is usually the appropriate choice.

Diagnose the error message

The error itself often narrows down the problem. Check these common outcomes before rebuilding the worksheet:

#N/A: An exact match was not found. Check spelling, spaces, data types, and whether the value exists in the first column of the selected range. If the formula uses approximate matching, verify that the first column is sorted correctly.
#REF!: The return-column number is larger than the number of columns in the selected table, or a referenced range has been deleted. Widen the range or correct the column number.
#VALUE!: The column number is less than 1, or an argument is otherwise invalid. The return-column number must be a positive whole number within the range.
#NAME?: Excel may not recognize the function name because of a spelling error, or it may be interpreting part of the formula as an unrecognized name. Check the formula text and any named ranges.

If the cell displays the formula itself rather than a result, it may be formatted as Text, or Show Formulas may be turned on. Change the cell format to General, edit the formula and press Enter. On Windows, Ctrl+` toggles Show Formulas; on Mac, the equivalent shortcut is Control+`. If only one formula is affected, check its cell format first. Also confirm that calculation is set to Automatic under Formulas > Calculation Options in Excel for Windows; Mac users can check Excel > Preferences > Calculation.

Check the lookup range and column number

Make sure the selected range begins with the column containing the lookup key. If IDs are in column B and the values to return are in column D, selecting A:D makes column A the search column, not B. Select B:D instead; within that range, column D is number 3. This leftmost-column rule is a limitation of VLOOKUP, not a setting that can be changed.

A wrong column number can return a plausible but unrelated result rather than an error. For example, a range spanning A:C with a column number of 2 returns column B. If column B contains a category and column C contains a price, the formula may successfully return the category even though you wanted the price. Count from the first column in the table range and verify the returned field against a known row.

Also watch for ranges that shift when formulas are copied. In the example formula, $A$2:$C$100 is an absolute reference: it stays put when copied. A relative range such as A2:C100 may shift to A3:C101 in the next row, potentially excluding the first record. Select the range in the formula and press F4 on Windows to cycle through reference styles; on Mac, use Command+T in many Excel setups, or type the dollar signs directly. Shortcut behavior can vary with keyboard settings.

Find spaces and hidden characters

Two values that look identical may differ because one contains a leading space, a trailing space, a line break, or a nonbreaking space copied from a webpage. Exact-match VLOOKUP treats those as different text. Compare the source key and lookup value in the formula bar, and inspect a few records on both sides of the lookup.

For a simple cleanup, suppose A2 contains the lookup text, including ordinary extra spaces or nonbreaking spaces around the word North. This formula replaces nonbreaking spaces with regular spaces, removes common control characters, and trims extra ordinary spaces; it returns North:

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

Apply the same cleanup approach to the lookup value and the keys in the table, ideally in helper columns, then run VLOOKUP against the cleaned keys. TRIM and CLEAN do not remove every possible special character, so a persistent mismatch may require identifying the exact character in the source data. Keep original data if it may be needed for auditing.

Make numbers and text use the same type

A numeric ID stored as text is not necessarily equal to the same digits stored as a number. For instance, a lookup value of numeric 104 may fail to match text 104 in the table. This commonly happens after importing CSV files, copying data from another system, or formatting one column as Text. Leading zeros matter too: 00104 and 104 are different identifiers when the zeros are significant.

Check whether values are left- or right-aligned by default, whether a green error indicator offers a number-conversion option, and whether both sides have the same intended format. Convert data consistently rather than changing only the result cell’s display format; formatting a cell as Number does not always convert text that was already stored there. For identifiers with meaningful leading zeros, retain text on both sides instead of converting them to numbers.

Check duplicates and verify the returned row

VLOOKUP returns the first matching row it encounters, searching downward from the top of the selected range. If a key appears more than once, the result may be valid for the first occurrence but not the record you expected. Sort order can affect which duplicate appears first, and changing the order can change the returned value.

Use Excel’s filter or Find command to inspect all occurrences of a repeated key. If the key should be unique, investigate and correct the source data rather than relying on row order. If the data intentionally contains multiple records per key, VLOOKUP is designed to return one result; a different approach may be needed to list or summarize all matching rows.

When to use a different lookup method

If the return column is to the left of the key, VLOOKUP cannot do the job with the usual table layout. In Microsoft 365, XLOOKUP can search one range and return a corresponding value from another, without requiring the search range to be leftmost. With codes in B2:B100, names in A2:A100, and the code in E2, this formula returns the name associated with an exact match:

=XLOOKUP(E2,B2:B100,A2:A100)

If E2 contains the code in B10 and A10 contains North, the result is North. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel releases such as Excel 2019. For compatible older versions, INDEX and MATCH can also look left. Consider switching methods when VLOOKUP’s leftmost-column requirement or fixed column number makes the workbook hard to maintain; for a straightforward exact lookup, a corrected VLOOKUP remains perfectly useful.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
Often, the displayed values differ underneath: one may contain a hidden space, a nonbreaking space, or a different data type. Confirm that the lookup value appears in the first column of the selected range, then compare the underlying text and number types.

Why is VLOOKUP returning the wrong result?
Check whether the fourth argument was omitted, whether the selected range starts in the correct column, and whether the return-column number points to the intended field. If the key occurs more than once, VLOOKUP returns the first match.

Does VLOOKUP search to the left?
No. It searches the first column of its table range and returns a value from that column or a column to its right. Use XLOOKUP in supported versions or an INDEX/MATCH approach if the needed return field is to the left.

Why does a copied VLOOKUP stop working?
The table reference may have shifted when you filled the formula down or across. Lock the range with absolute references such as $A$2:$C$100, and check that the lookup-value reference changes as intended.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is the usual choice for names and IDs. TRUE, or an omitted fourth argument, requests approximate matching and is suitable only for correctly ordered threshold tables.

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.