Why Your VLOOKUP Is Not Working (And How to Fix It)

Quick answer: VLOOKUP breaks in one of five predictable ways — the lookup value does not actually exist in the first column of the table array, a text/number type mismatch stops an exact match, hidden leading/trailing spaces block the comparison, the range_lookup argument was left blank (which defaults to approximate match, not exact), or the col_index_num points past the edge of the table. Check those five before assuming the formula itself is broken.

VLOOKUP only searches the first column of the table array and returns a value by column position, not by header name — that trips up anyone coming from SUMIFS-style thinking or Power Query joins where field names matter more than position. For broader formula errors outside of lookups (#DIV/0!, #VALUE!, circular references), see common Excel errors and fixes — this post is specifically about lookup failures. For workbook-wide slowdowns from thousands of volatile or whole-column lookups, see why Excel files get slow.

In production models, lookup failures are usually data-governance failures, not formula bugs: inconsistent IDs, mixed types after a CSV import, or an undocumented assumption about sort order. Stabilize the source keys first, then optimize the formula — otherwise you just trade one visible #N/A for a silent wrong match that is much harder to catch.

Symptom, cause, and fix

• Symptom: VLOOKUP returns #N/A even though you can see the value in the table. Cause: A type mismatch (text vs. number) or hidden spaces/characters mean the values are not actually identical to Excel. Fix: Wrap both sides in TRIM(), or convert types to match with VALUE() or TEXT().
• Symptom: VLOOKUP returns a value, but it is the wrong one. Cause: range_lookup was left blank or set to TRUE on unsorted data, so Excel returned the nearest match instead of an exact one. Fix: Add FALSE as the fourth argument for exact match.
• Symptom: VLOOKUP returns #REF!. Cause: col_index_num is larger than the number of columns actually included in the table array — often after a column was deleted from the source range. Fix: Recount columns from the left (the first column is 1) and confirm the table array still includes every column you reference.
• Symptom: VLOOKUP returns 0 instead of blank for empty source cells. Cause: Excel returns a literal 0 when the matched cell is empty; this is expected behavior, not an error. Fix: Wrap the formula in an IF check, e.g., =IF(VLOOKUP(...)="","",VLOOKUP(...)), or use IFERROR combined with a blank check.
• Symptom: The formula worked yesterday, now shows the same stale result no matter what changes. Cause: Calculation mode is set to Manual, or the cell is formatted as text so Excel is not evaluating it as a formula at all. Fix: Set calculation to Automatic (Formulas tab), then press F9; if it still will not update, re-enter the formula after changing the cell format to General.
• Symptom: VLOOKUP worked fine, then broke after the file was moved, renamed, or shared. Cause: The table array points to an external workbook reference that no longer resolves. Fix: Check Data → Edit Links for broken external references, and re-point or rebuild the reference.

Common VLOOKUP problems

1. #N/A error

Causes: the lookup value genuinely does not exist in the first column, an exact match was requested but not found, or a data type mismatch (text "1024" vs. number 1024) is preventing a match Excel would otherwise consider identical.

Solutions:

• Verify the lookup value exists in the table, including checking for a trailing space or invisible character copied in from a CSV export.
• Confirm you are requesting exact match (FALSE), not approximate.
• Match data types explicitly — convert with VALUE() to force text to number, or TEXT() to force number to text.
• Remove leading/trailing spaces with TRIM() around both the lookup value and, if needed, the source column.

Real example: In a workbook built from a legacy accounting export, roughly 15% of rows returned #N/A because product IDs were stored as text with leading zeros in one sheet and as plain numbers in another. Wrapping both sides of the lookup in TEXT(cell,"0") resolved every mismatch without touching the source data.

2. Incorrect results (no error, wrong answer)

This is the more dangerous failure mode — the formula returns a value, so nothing looks broken, but it is the wrong one.

Causes: approximate match (TRUE, or the argument omitted) used on unsorted data, an off-by-one error in the column index, or formatting differences that make two visually identical values not actually equal.

Solutions:

• Use FALSE explicitly for exact match unless you specifically need approximate match on sorted numeric bands (tax brackets, pricing tiers).
• Recount the column index from the left edge of the table array, not from the worksheet's column letters.
• Check for formatting inconsistencies — a date stored as text will not match a true date value even if it displays identically.
• Sort the source data if you are intentionally using approximate match.

3. Formula returns #REF!

Causes: col_index_num exceeds the number of columns in the table array, or a column was deleted from the source range after the formula was written.

Solutions:

• Recount columns from the left; the first column in the table array is always 1, regardless of its worksheet column letter.
• Verify the table array reference still includes every column you point to.
• Use the COLUMN() function against your target header to double-check the index rather than counting by eye.
• Check whether a column was deleted or moved since the formula was last edited — this is the single most common cause of a previously working VLOOKUP suddenly returning #REF!.

4. VLOOKUP not updating

Causes: calculation mode set to Manual, the formula entered or pasted in as text rather than a live formula, or a broken external reference.

Solutions:

• Set calculation to Automatic under the Formulas tab.
• Press F9 to force a full recalculation.
• Re-enter the formula if the cell was formatted as Text before the formula was typed — reformatting alone will not fix an already-text cell.
• Check Data → Edit Links for external file references that no longer resolve.

Data type issues

Text vs. numbers

Problem: the lookup value is text but the table stores numbers, or vice versa — visually identical but not equal to Excel.

• Convert text to number: =VALUE(A2)
• Convert number to text: =TEXT(A2, "0")
• Standardize formatting across the source and lookup columns rather than fixing it formula-by-formula.
• Check for hidden characters brought in from a CSV or copy-paste from a web page — these do not show visually but block an exact match.

Leading and trailing spaces

Problem: invisible spaces prevent an otherwise-identical value from matching exactly.

• Use TRIM() around the lookup value: =VLOOKUP(TRIM(A2), Table, 2, FALSE)
• Clean the source data at the point of import rather than repeatedly wrapping every downstream formula.
• Use Find & Replace to strip spaces in bulk when the source is static.
• Apply TRIM() to both the lookup value and the table's first column if the source is not fully under your control.

Case sensitivity

VLOOKUP is not case-sensitive — "APPLE" matches "apple" without complaint. If case genuinely matters for your logic, VLOOKUP cannot enforce it; use INDEX/MATCH combined with EXACT() instead.

Formula syntax issues

Correct VLOOKUP syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters:

• lookup_value: the value to search for.
• table_array: the range containing your data, with the lookup column as its first column.
• col_index_num: the column number to return from, counted from the left edge of table_array.
• range_lookup: FALSE for exact match, TRUE (or omitted) for approximate match on sorted data.

Common syntax errors

1. Missing $ signs — without absolute references, the table array shifts when the formula is copied down, breaking every row after the first.
2. Wrong column number — counted from the worksheet's column letters instead of from the left edge of the table array.
3. Table array excludes the lookup column — the range must start at the column containing the value you are searching for, not the column you want returned.
4. Missing FALSE — omitting the fourth argument silently defaults to approximate match, which is rarely what a lookup-by-ID formula actually wants.

Edge cases that trip people up

Can VLOOKUP look to the left?

No. VLOOKUP always returns a value from a column to the right of the lookup column, by design — it cannot look left. If the value you need sits to the left of your lookup column, VLOOKUP cannot do it directly; restructure the table, duplicate the lookup column to the right side, or switch to INDEX/MATCH, which has no directional restriction.

VLOOKUP with wildcards

VLOOKUP supports wildcards (`*` and `?`) in the lookup value, but only when range_lookup is set to FALSE, and only if wildcard matching has not been disabled in Excel's formula options. A wildcard lookup against a large table is also considerably slower than an exact match, since Excel cannot use a fast lookup path for partial-text search.

VLOOKUP across sheets or workbooks

A VLOOKUP referencing another open workbook works fine while both files are open, but breaks or shows stale cached values once the source workbook is closed or moved. For anything shared outside your own machine, either bring the reference table into the same workbook or rebuild the link with Power Query, which handles broken source paths more gracefully than a raw external-reference formula.

VLOOKUP with merged cells

Merged cells inside a table array quietly break VLOOKUP's row alignment — Excel treats a merged range as a single cell attached to its top-left corner, so the values you expect on the merged rows below simply are not there. Unmerge the source table before using it as a lookup range; this is one of the most common "it should work but does not" causes we see in inherited spreadsheets.

Better alternatives

INDEX/MATCH combination

Advantages: works with unsorted data using exact match without a performance penalty, can look up to the left as easily as the right, and does not break when a column is inserted between the lookup and return columns the way a hardcoded col_index_num can.

Example:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

XLOOKUP (Excel 365)

Advantages: simpler single-formula syntax, defaults to exact match instead of approximate, can look up in either direction, and includes a built-in "if not found" argument that removes the need to wrap every formula in IFERROR().

Example:

=XLOOKUP(lookup_value, lookup_array, return_array)

If you are maintaining a workbook that has to stay compatible with older Excel versions or Google Sheets imports, INDEX/MATCH is still the safer default; XLOOKUP is the better choice when you know every user is on Excel 365.

Troubleshooting checklist

1. Verify the lookup value actually exists in the table.
2. Check that data types match between the lookup value and the table's first column.
3. Remove spaces with TRIM() on both sides of the comparison.
4. Use FALSE for exact match unless you specifically need approximate.
5. Verify the column index number, counted from the left edge of the table array.
6. Confirm the table array includes the full range you are referencing.
7. Make sure calculation is set to Automatic.
8. Test the formula against one known-good row before rolling it out across the sheet.
9. Confirm source IDs are actually unique where your logic assumes uniqueness.
10. Check whether locale settings silently transformed dates or decimals during import.

Best practices

1. Use Tables or named ranges instead of raw cell references, so the lookup range grows automatically as data is added.
2. Lock references with $ signs so the table array does not shift when the formula is copied.
3. Default to exact match (FALSE) unless you specifically need approximate match on sorted numeric bands.
4. Wrap in IFERROR() for user-facing sheets, but only after confirming the underlying data issue — do not use IFERROR to hide a mismatch you have not actually diagnosed.
5. Document assumptions — a comment noting "table must stay sorted by ID" saves the next person hours of debugging.
6. Normalize keys before lookup — apply TRIM/CLEAN/UPPER consistently at the point of import rather than patching individual formulas.
7. Add control checks — compare lookup hit counts against source row totals after every refresh to catch silent mismatches early.

Frequently asked questions

Why does my VLOOKUP show #N/A even though the value is clearly there?

Almost always a type mismatch or hidden characters — the value looks identical but is not, to Excel, because one side is text and the other is a number, or one has a trailing space from a CSV import.

Why is my VLOOKUP returning the wrong value instead of an error?

This usually means range_lookup was left blank or set to TRUE, so Excel found the nearest match on unsorted data instead of failing outright. Add FALSE as the fourth argument to force an exact match.

Can VLOOKUP look to the left of the lookup column?

No — VLOOKUP can only return values from columns to the right of the lookup column. Use INDEX/MATCH or XLOOKUP if you need to look left.

Why does VLOOKUP return 0 instead of blank?

Excel returns a literal 0 when the matched source cell is empty; that is expected behavior. Wrap the formula in an IF check to display a blank instead.

Is VLOOKUP case-sensitive?

No. "APPLE" and "apple" are treated as the same value. If case needs to matter, use INDEX/MATCH combined with EXACT().

Why did my VLOOKUP break after I moved or renamed the file?

The table array likely points to an external workbook reference. Check Data → Edit Links to find and repair broken references.

What is the actual difference between VLOOKUP TRUE and FALSE?

FALSE requires an exact match and returns #N/A if none exists. TRUE (or omitting the argument) returns the closest match on the assumption the data is sorted ascending — on unsorted data, TRUE silently returns a wrong answer instead of an error.

Should I switch from VLOOKUP to XLOOKUP or INDEX/MATCH?

For new models on Excel 365, XLOOKUP is generally the better default. For workbooks that need to stay compatible with older Excel versions, Google Sheets, or teams on mixed versions, INDEX/MATCH remains the safer, more portable choice.

When to seek professional help

If VLOOKUP issues keep surfacing across a portfolio of workbooks, that is a modeling problem, not a formula problem — another patch will not fix it. Excel consulting can standardize lookup patterns across your files, align with structuring large Excel data models, and help you compare when a move from Excel to a database is actually cheaper than continued formula surgery. For the cost case behind that decision, see the cost of manual spreadsheet errors.