Excel VLOOKUP Not Working? Troubleshoot and Fix Common Issues
1. Common Causes of VLOOKUP Failures
| Issue | Why it breaks VLOOKUP | Quick fix |
|---|---|---|
| Lookup value not an exact match | VLOOKUP needs an exact match unless the fourth argument (range_lookup) is set to TRUE. Extra spaces, different case, or hidden characters make the lookup fail. |
Use TRIM()/CLEAN() on both the lookup column and the search key, or force an exact match with FALSE. |
| Wrong column‑index number | col_index_num is counted from the leftmost column of the table array. A number larger than the array width returns #REF!. |
Count the columns in the array and enter the correct index, or expand the range. |
| Lookup column not leftmost | VLOOKUP always searches the first column of the table array. If the key column is to the right, no match is found. | Move the key column to the left, or switch to INDEX/MATCH or XLOOKUP (Excel 365/2021, Google Sheets 2022+). |
| Data‑type mismatch | Text "123" does not equal numeric 123. |
Convert both sides with VALUE() (text → number) or TEXT(...,"0") (number → text). |
| Range‑lookup flag omitted or set to TRUE | Without FALSE, VLOOKUP performs an approximate match and expects the lookup column to be sorted ascending. Unsorted data yields wrong results or #N/A. |
Always include FALSE (or 0) unless you deliberately need an approximate lookup. |
| Hidden rows/filters | Filters hide rows from view but not from VLOOKUP; however, if the matching row is filtered out, you’ll see #N/A. |
Clear filters or use formulas that respect visibility (e.g., SUBTOTAL). |
| Incorrect table array | A range that omits the lookup column or the return column produces #N/A or #REF!. |
Verify the range (e.g., $A$2:$D$100) or use a structured table reference. |
| Merged cells in the lookup column | Merged cells break the contiguous range VLOOKUP expects. | Unmerge all cells in the lookup column. |
| Duplicate keys | VLOOKUP returns the first match it finds, which may not be the row you expect. | Remove duplicates or use INDEX/MATCH with a more specific match condition. |
2. Preparing Data for VLOOKUP
VLOOKUP works only when the source table follows a simple layout. Spend a few minutes cleaning the data before you write the formula.
| Step | Action | Reason |
|---|---|---|
| 1️⃣ Place the lookup column on the left | Make the key column the first column of the range. | VLOOKUP can only search the leftmost column. |
| 2️⃣ Remove duplicate keys | Keep each lookup value unique, or decide which duplicate you want to return. | Otherwise VLOOKUP returns the first occurrence. |
| 3️⃣ Trim and clean text | Apply TRIM() and CLEAN() (or =TRIM(CLEAN(A2))). |
Invisible characters cause #N/A. |
| 4️⃣ Make data types consistent | Convert numbers stored as text, format dates uniformly, etc. | Mismatched types prevent matches. |
| 5️⃣ Sort only when using approximate match | If you plan to set range_lookup to TRUE, sort the key column ascending. |
Approximate lookups rely on sorted data. |
| 6️⃣ Unmerge cells | Ensure each lookup value occupies a single cell. | Merged cells break the lookup algorithm. |
Quick “before & after” example
Problematic raw data
| A | B | C |
|---|---|---|
| 101 | Apple | 0.5 |
| 102 (space) | Banana | 0.8 |
| 103 | Cherry | 0.6 |
| 101 | Apple | 0.5 |
Cleaned data
| A | B | C |
|---|---|---|
| 101 | Apple | 0.5 |
| 102 | Banana | 0.8 |
| 103 | Cherry | 0.6 |
Now the lookup column (A) is leftmost, spaces are removed, and duplicates are eliminated.
3. Using Exact Match with VLOOKUP
Exact match is the safest mode because it works on unsorted data and returns #N/A when no perfect match exists.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
| Argument | What to enter |
|---|---|
lookup_value |
The value you are searching for (e.g., "Apple"). |
table_array |
The range that includes the lookup column and the column to return (e.g., A2:C6). |
col_index_num |
Column number within table_array to return (1 = lookup column, 2 = first result column, etc.). |
FALSE (or 0) |
Forces an exact match. |
Example
| A (Product) | B (Price) |
|---|---|
| Apple | 1.20 |
| Banana | 0.75 |
| Cherry | 2.10 |
| Date | 1.80 |
| Elderberry | 3.00 |
=VLOOKUP("Cherry", A2:B6, 2, FALSE)
Result
2.10
If FALSE were omitted, the function would look for the largest value ≤ “Cherry” in a sorted list, which could return the wrong price or #N/A.
Common pitfalls (2‑sentence tips)
Trailing spaces – Use TRIM(CLEAN(A2)) on both the lookup value and the table column.
Numbers stored as text – Wrap the lookup value with VALUE() or format the column as Number.
4. Modern Alternatives: XLOOKUP and INDEX/MATCH
When VLOOKUP feels limiting, two alternatives provide more flexibility.
| Feature | XLOOKUP (Excel 365/2021, Sheets 2022+) | INDEX + MATCH (all versions) |
|---|---|---|
| Search direction | Left‑to‑right or right‑to‑left | Any direction (requires separate MATCH) |
| Default exact match | Yes (no extra argument) | Must set 0 in MATCH |
| Return multiple columns | return_array argument |
Needs additional INDEX calls or array formulas |
| Custom “not found” text | if_not_found argument |
Wrap with IFERROR |
| Availability | Excel 365/2021, Google Sheets via add‑on | Every Excel version, every Google Sheets version |
XLOOKUP example (Excel 365)
=XLOOKUP(A2, Products[ID], Products[Price], "Not found")
| A (Lookup ID) | Products[ID] | Products[Price] |
|---|---|---|
| 102 | 101 | 12.99 |
| 102 | 8.50 | |
| 103 | 15.75 |
Result: 8.50 (price for ID 102). If the ID isn’t present, the formula returns "Not found" instead of #N/A.
INDEX/MATCH example (works everywhere)
=INDEX(Products[Price], MATCH(A2, Products[ID], 0))
The same table returns 8.50. MATCH finds the row number of the lookup value (0 forces exact match); INDEX returns the price from that row.
When to choose which
- Use XLOOKUP for clean, single‑function formulas and when you need a custom “not found” message or to return whole rows/columns.
- Use INDEX/MATCH on older Excel versions, in Google Sheets without the XLOOKUP add‑on, or when you need a “reverse lookup” without extra tricks.
5. Advanced Troubleshooting Tips
If the basic fixes don’t solve the problem, the issue is often hidden in the formula or workbook settings.
| Issue | Why it Happens | Fix (Excel) | Fix (Google Sheets) |
|---|---|---|---|
| Exact vs. approximate match | Omitting the fourth argument defaults to TRUE. |
Add FALSE (or 0): =VLOOKUP(A2,Table!$A$2:$D$100,2,FALSE) |
Same syntax works. |
| Column index out of range | col_index_num exceeds the width of table_array. |
Expand the range or lower the index number. | Same fix. |
| Number stored as text | "123" ≠ 123. |
Coerce both sides: =VLOOKUP(--A2,Table!$A$2:$D$100,2,FALSE) (double‑unary forces numeric) |
=VLOOKUP(VALUE(A2),A2:D100,2,FALSE) |
| Hidden characters | Non‑breaking spaces or line breaks from copy‑paste. | Clean with TRIM(CLEAN()): =VLOOKUP(TRIM(CLEAN(A2)),Table!$A$2:$D$100,2,FALSE) |
Same functions exist in Sheets. |
| Dynamic arrays / spilled ranges (Excel 365/2021) | A spilled range may be larger than expected, causing mismatched column indexes. | Reference the spill range explicitly (e.g., A2#:C2#) or wrap with @ to force a single cell. |
Sheets automatically spills; use INDEX to pick a single cell if needed. |
| Lookup value in a filtered table | Filters hide rows, but VLOOKUP still sees them; if the hidden row contains the match, you get #N/A. |
Use AGGREGATE(15,6,ROW(Table[Key])/(Table[Key]=A2),1) to get the visible row number, then INDEX. |
Sheets: FILTER combined with INDEX can emulate the same behavior. |
6. FAQ
Why does VLOOKUP return #N/A in my sheet?
#N/A means VLOOKUP couldn’t find an exact match for the lookup value. Common reasons are extra spaces, mismatched data types (text vs. number), or the lookup column not containing the searched value. Verify that the lookup value exists exactly as written in the leftmost column of the table array.
How can I make VLOOKUP search from the right side?
VLOOKUP can’t look left; it only searches the first column of the range. To retrieve data from a column left of the key, either rearrange the table so the key is first, or use INDEX/MATCH (or XLOOKUP, which supports reverse lookups).
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP is limited to left‑to‑right searches, requires a column index number, and defaults to approximate match when the fourth argument is omitted. XLOOKUP (available in Excel 365/2021 and Google Sheets 2022+ via add‑on) searches in either direction, returns a custom “not found” value, and does not need a separate index argument.
How do I handle duplicate lookup values?
When duplicates exist, VLOOKUP returns the first match it encounters, which may be unpredictable. Remove duplicates if possible, or switch to INDEX/MATCH where you can combine MATCH with a helper column (e.g., row number) to target a specific occurrence.
Remember: Most VLOOKUP failures are data‑quality issues—trim spaces, unify data types, and keep the lookup column leftmost. When those basics are solid, you can rely on VLOOKUP or move to XLOOKUP/INDEX‑MATCH for more power.