Most VLOOKUP failures stem from type mismatches, a mis-set range, or the last argument, not the app.
Nothing stalls a spreadsheet like a lookup that keeps throwing errors or pulls the wrong value. The function itself is dependable; the usual culprits are setup choices, mixed data types, or a small oversight in the arguments. This guide shows the exact checks that clear those roadblocks, with compact fixes you can apply in minutes.
Vlookup Not Working — What To Check First
Start with the parts that break lookups most often: the last argument (match mode), the layout of the table array, and the data type of the lookup value and first column. Then verify the return column index and anchor your ranges. The table below maps common symptoms to likely causes and fast fixes.
Quick Triage Table
| Symptom | Likely Cause | Quick Fix |
|---|---|---|
| #N/A error | Value not found; hidden spaces; text/number mismatch; wrong match mode | Clean spaces; align types; switch to exact match; confirm value exists |
| Wrong row returned | Using approximate match on unsorted data | Sort the first column A→Z/0→9 or switch to exact match |
| Blank result | Return column points to empty cells; formula evaluates to empty text | Check column index; trace precedents; replace empty with a default |
| #REF! error | Column index exceeds the table width | Reduce index or expand the table array |
| #VALUE! error | Bad argument types; malformed range | Re-enter ranges; confirm separators and absolute refs |
| Works for some values only | Inconsistent formatting; mixed text/number in the first column | Standardize formats; coerce types with helper formulas |
Set The Right Match Mode
The fourth argument controls matching. Leave it blank or set TRUE and the function uses an approximate match that assumes the first column is sorted. Set it to FALSE for exact matching.
- Exact match (FALSE): Finds only precise keys. Best for IDs, SKUs, emails.
- Approximate match (TRUE): Finds the largest key ≤ lookup value. Best for banded rates or score brackets, but the first column must be sorted.
Seeing odd returns or the “next lower” band when you expected a clean match? You’re likely in approximate mode on unsorted data. Either sort the first column or flip the last argument to FALSE.
Confirm The Table Layout
The function reads the first column of the table array as the key column, then counts to the right to return a value. If the key you need sits to the right of what you want to return, the classic form can’t read left. Use one of these moves:
- Rebuild the table so the key column sits on the left.
- Switch to a flexible pair like
INDEX+MATCHthat can return left or right.
Check The Column Index
The third argument is a 1-based index within the table array. If your array is A:D, an index of 4 returns column D. If the index is larger than the table width, you’ll get #REF!. Count again, or expand the array.
Fix Text/Number Mismatches
One side treats a key as text; the other treats it as a number. They look the same to your eye, but not to the engine. Symptoms include #N/A for clear matches or a formula that only works after retyping a cell.
How To Align Types Fast
- Coerce to number: Wrap the key with
--orVALUE(), or multiply by1. - Coerce to text: Wrap with
TEXT(key,"0")or append&"". - Strip spaces: Apply
TRIM()to keys and source; useCLEAN()for non-printing characters.
In Sheets, a column set to Plain text can block matches. Switch both columns to Number or to the same format, then recalc.
Eliminate Hidden Characters
Copied data often carries thin spaces, non-breaking spaces, or control characters. These break exact matches.
- Excel:
TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),\" \")))clears common offenders. - Sheets: Use
TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),\" \")))as well; both apps support these functions.
After cleaning, paste values to lock the normalized keys.
Lock Your Ranges
Fill a formula down and the table array might shift if you used relative references. Convert the array to absolute refs with $ signs ($A$2:$D$500). The same goes for the lookup value cell when needed.
Sort Rules For Approximate Match
Approximate mode uses a binary search and expects the first column sorted ascending. If it isn’t, the function can hop to the wrong row.
- Excel: Sort the first column A→Z or smallest→largest.
- Sheets: Use Data → Sort range; check that the range includes the entire table.
Catch And Handle Errors
Some lookups will miss. Wrap the call with IFERROR() to show a friendly message or a default value. That masks the noise while you clean source data.
=IFERROR(VLOOKUP($E$2,$A$2:$C$100,3,FALSE),"Not found")Use Helpers When Keys Aren’t Unique
If the first column contains duplicates, you might pull a neighbor of the first match rather than the row you meant. Create a helper key that makes each row unique:
- Build a composite key with
CONCATor&(eg. CustomerID & “-” & Date). - Point the lookup to that helper column.
Regional Separators And Formula Syntax
Some locales use semicolons instead of commas in function arguments. The app shows the correct separator hint as you type. If you see errors after pasting a formula from the web, swap separators to match your locale.
When You Need A Left Return
Classic syntax can’t return a column to the left of the key. You have two clean routes:
- Reshape the table so the key sits in the leftmost position.
- Use
INDEXwithMATCHto target any column by position.
Precision Pitfalls With Numbers
Floating-point values that look equal can differ at the 15th decimal place. For banded prices or thresholds, round the lookup value and the key column to the same decimal places. That removes phantom mismatches.
Reference Hygiene: Absolute vs Relative
Mis-anchored references are a silent source of chaos. A formula copied across can shift the table array or the return column. Press F4 to toggle $ anchors while editing the range. Test one cell, then fill down again.
Two Links Worth Bookmarking
For exact argument rules, edge-case notes, and more examples, see the official VLOOKUP function guide. Working in Google’s editor? The Google Sheets VLOOKUP help page spells out syntax, separators, and sample use.
Deep Dive: Exact Vs Approximate Settings
Pick the mode based on the shape of your data and the decision you’re making. The matrix below summarizes when each shines and the setup each one needs.
Match Mode, Use Cases, Setup
| Mode | Best Use | Setup Notes |
|---|---|---|
| FALSE (exact) | IDs, exact SKUs, emails, tags | No need to sort; strings & numbers must match type; clean spaces |
| TRUE (approx.) | Tax bands, score brackets, tiered pricing | Sort first column ascending; use thresholds; ensure ranges cover all values |
| INDEX+MATCH | Left returns, dynamic columns, large tables | No left/right limits; can match on multiple keys; more flexible layout |
Cleaning Recipe: Make Stubborn Matches Work
When you face a sheet with mixed data from exports and manual entry, follow this short recipe:
- Clone the sheet so you can test freely.
- Create a helper column next to the key column; apply
TRIM(CLEAN(SUBSTITUTE([key],CHAR(160)," "))). - Coerce types in both the key and the lookup value cells to text or number, consistently.
- Anchor the table array with
$signs; verify the return column index. - Test both match modes: try FALSE first; if you truly need bands, sort and try TRUE.
- Wrap with IFERROR to contain noise while you finish cleanup.
Performance Tips On Large Tables
Big files can slow down when thousands of lookups recalc. A few quick wins:
- Convert the source to an official Table and refer to structured names; it keeps ranges tight.
- Use approximate match with a sorted key column for tier lookups; it calculates faster.
- Reduce volatile functions near your lookup cells; they trigger extra recalcs.
Common Formula Patterns You Can Reuse
Exact Match With A Friendly Default
=IFERROR(VLOOKUP($B$2,$A$2:$D$500,4,FALSE),"No match")Band Lookup With Sorted Thresholds
=VLOOKUP($E$2,$A$2:$B$10,2,TRUE)Make sure the first column contains thresholds like 0, 60, 70, 80, 90 and is sorted.
Case-Aware Match
The function isn’t case-sensitive. If letter case matters, switch to INDEX+MATCH with an exact case test:
=INDEX(ReturnCol, MATCH(TRUE, EXACT(KeyCol, LookupCell), 0))Troubleshooting Checklist
- Does the first column of the table hold the key you’re searching?
- Is the return column index inside the width of the table array?
- Are you on the right match mode for this job?
- Are the key and first column the same type (both text or both numbers)?
- Did you strip spaces and non-printing characters?
- Are your ranges locked with
$for fills and copies? - For banded logic, is the first column sorted?
When To Switch Tactics
If you need two-way lookups, left returns, or multiple conditions, you’ll spend less time with INDEX+MATCH or a newer lookup that accepts flexible ranges. Keep the classic form for simple, stable keys and one-column returns; reach for the flexible pair when layouts get complex.
Final Word: Make The Function Work For You
Most fixes come down to three habits: pick the right match mode, align types, and lock the ranges. Add a quick clean step for pasted data and you’ll stop chasing ghosts across the sheet.