Excel VLOOKUP Not Working for One Column? How to Fix It

If VLOOKUP works for other return columns but fails for one, check that column’s index number, the lookup values and the source range: a one-column failure often comes from a wrong return index or inconsistent data in that column. For an exact match, set the final argument to FALSE and make sure the lookup value is in the first column of the selected range.

What a one-column VLOOKUP problem usually means

VLOOKUP searches down the first column in a specified range, finds a matching value, then returns a value from another column in the same row. The return-column number is counted from the left edge of the selected range, not from the worksheet’s column letters. So, if the range starts in column A, column C is the third column in that range.

When one requested column fails while another works, the lookup itself may be finding the right row. The trouble may be that the formula points to the wrong return column, that cells in the troublesome column contain errors or unusual values, or that a copied formula has changed its range. If the result is #N/A, focus first on whether the lookup value matches an entry in the first range column. If the result is a plausible but incorrect value, check the column index and match mode.

For example, with IDs in A, names in B, prices in C, and a lookup ID in E2, the following formula returns the price from column C:

With IDs in A2:A100, names in B2:B100, prices in C2:C100, and the ID to find in E2: =VLOOKUP(E2,$A$2:$C$100,3,FALSE)

The 3 means “return the third column of A:C,” which is C. To return a name from B using the same range, the index would be 2. FALSE requests an exact match; it avoids the approximate-match behavior that can produce a surprising result when the data is not sorted.

Check the return-column index and range

A VLOOKUP index is relative to the first column in table_array. This is a frequent source of errors when the selected range does not start in column A. If the range is B:D, then B is index 1, C is index 2, and D is index 3. Counting from the worksheet edge would lead to the wrong answer.

Check the formula’s second and third arguments together. The range must include both the lookup column and the column whose result you want, and the index must point to that result within the range. VLOOKUP cannot return a value to the left of its lookup column. If you need that, use a different formula or rearrange the lookup range.

An index larger than the number of columns in the range typically produces #REF!. An index below 1 produces #VALUE!. A valid index can still return the wrong field if you miscounted. When you copy a formula across columns, inspect whether the index number changes as intended; a fixed number such as 3 does not automatically become 4 when copied right.

Also look for a range that stops short of the problem column. If the selected range is A:C, an index of 4 is invalid because the range contains only three columns. A range that starts or ends on the wrong row can also pair the lookup key with an unintended result.

Use exact match unless approximate match is intended

The fourth VLOOKUP argument controls the match mode. FALSE (or 0) asks Excel for an exact match. TRUE, 1, or an omitted fourth argument requests approximate matching. Approximate matching is designed for cases such as tax bands or graded thresholds, and normally requires the first column to be sorted in ascending order. With unsorted values, it can return an unexpected row.

For ordinary IDs, product codes, names, and invoice numbers, explicitly use FALSE. This is especially important when one return column seems unreliable: the column may be exposing a wrong-row match rather than having a problem of its own. Remember that exact matching in VLOOKUP is not case-sensitive, so “abc” and “ABC” count as a match.

For example, with employee IDs in A2:A100 and departments in B2:B100, this formula requests an exact department match for the ID in E2:

With employee IDs in A2:A100, departments in B2:B100, and a lookup ID in E2: =VLOOKUP(E2,$A$2:$B$100,2,FALSE)

Look for text, number, and hidden-character differences

Values that look identical on screen may not be identical to Excel. A numeric ID stored as text may fail to match the same digits stored as a number. Leading or trailing spaces can also prevent an exact match. Data copied from websites or other systems may contain a nonbreaking space, which looks like a normal space but is not removed by every ordinary cleanup step.

Check a failing key in both the lookup cell and the source column. Compare the formula bar contents, cell formats, and whether the values are left- or right-aligned, but treat alignment only as a clue: formatting can change alignment without changing the underlying value. Try searching the source column for the key, or use a temporary check such as COUNTIF to see whether Excel finds an equal value.

With source keys in A2:A100 and the lookup value in E2, this check returns the number of exact matches Excel finds in that range:

With possible lookup keys in A2:A100 and a search key in E2: =COUNTIF($A$2:$A$100,E2)

A result of 0 suggests a mismatch or a key that is not present; a result greater than 1 also reveals duplicates. COUNTIF is a diagnostic, not proof that every VLOOKUP detail is correct. For numeric text, Data > Text to Columns can convert a selected column when the values are consistently structured; review the preview and conversion before applying it. Alternatively, standardize both sides of the lookup so they use the same type.

For copied data with nonbreaking spaces, a helper column can replace those characters with ordinary spaces and trim leading, trailing, and repeated ordinary spaces. Apply equivalent cleanup to the lookup value as well as the source keys; cleaning only one side can leave the mismatch in place. TRIM also changes repeated internal spaces, so do not use it if those spaces are meaningful parts of a key.

With a text key in A2 that may contain nonbreaking spaces, this helper formula normalizes it for comparison:

With the potentially dirty source key in A2: =TRIM(SUBSTITUTE(A2,CHAR(160)," "))

Diagnose errors and incorrect results

The exact symptom narrows down the cause:

#N/A: No exact match was found, the lookup value is absent, or the key has a type or character mismatch. Confirm that VLOOKUP is searching the correct first column.
#REF!: The return index points beyond the selected range, or a referenced column or range has been removed.
#VALUE!: The return index may be less than 1, or another argument is invalid. Check the formula arguments and their references.
A value from the wrong row: Look for an omitted fourth argument, approximate matching on unsorted data, or a duplicate key. With duplicates, VLOOKUP returns the first matching row it encounters.
A value from the wrong column: Recount the index from the left edge of table_array, and verify the range includes the intended return column.

VLOOKUP generally returns the first matching row, so a duplicate key can make a formula appear inconsistent if the duplicate records have different values in the return column. Filter or sort the source data to inspect duplicates, then decide which record should be used. Do not assume that changing the return-column number will resolve a duplicate-key issue.

If the formula is visibly displayed in the cell instead of calculating, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change the cell format to General if needed, then re-enter the formula. A leading apostrophe can also cause Excel to treat an entry as text.

Fix formulas that change when copied

When copying a lookup formula down a list, the lookup cell should normally move with each row, while the source table should stay fixed. Dollar signs create absolute references: in $A$2:$C$100, both the column and row boundaries remain fixed when the formula is copied. Without them, the lookup range may shift and eventually exclude the needed key or return column.

If one formula works and the next row does not, compare the formulas in the formula bar. Check for a shifted table range, a blank or malformed lookup value in that row, and a manually changed index. For a table that grows over time, an Excel Table with structured references can make the source range easier to maintain, but the lookup column still needs to be first for VLOOKUP.

Use a small, controlled test: select a row whose answer you know, verify the key in the source table, and confirm the return column by reading across that same row. Then inspect the failing row. This separates a formula-structure problem from a single bad record, which may need to be corrected in the source data rather than hidden with an error-handling formula.

When to use XLOOKUP or another alternative

If the requested return column sits to the left of the lookup column, or if the VLOOKUP index is difficult to maintain, XLOOKUP can be clearer. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It searches a lookup array and returns from a separate return array, so the return array does not have to be to the right.

With IDs in C2:C100, prices in A2:A100, and the ID to find in E2, this formula returns the matching price from the column to the left:

With lookup IDs in C2:C100, return prices in A2:A100, and the search ID in E2: =XLOOKUP(E2,C2:C100,A2:A100,"Not found")

INDEX and MATCH are another option in versions that support those functions. They can separate the lookup position from the return range and work when the return column is left of the lookup column. Changing functions does not automatically repair dirty or mismatched keys, duplicate records, or incorrect source data; those issues still need attention.

Frequently Asked Questions

Why does VLOOKUP work for some columns but not another?
The return index may be wrong, the selected range may not include the desired column, or the values in that particular return column may be blank or contain errors. If the formula shows #N/A, check the lookup key and first range column too.

Does VLOOKUP need the lookup column to be first?
Yes. VLOOKUP searches only the leftmost column of its table_array and returns a value from a column to its right. Use XLOOKUP or INDEX and MATCH if the return data is to the left.

Why does VLOOKUP return the wrong result instead of an error?
An omitted fourth argument can trigger approximate matching, which is unsuitable for unsorted exact-match data. Duplicate keys can also make VLOOKUP return the first matching record rather than another duplicate farther down.

Can extra spaces cause VLOOKUP to return #N/A?
Yes. Leading, trailing, repeated, or nonbreaking spaces can make two visually similar text values differ. Normalize the lookup value and source keys consistently, taking care not to remove spaces that are meaningful.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP’s text matching is not case-sensitive. If capitalization must distinguish two keys, use a case-sensitive approach rather than relying on VLOOKUP.

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.