Stop Using VLOOKUP Wrong: Common Mistakes and How to Fix Them

VLOOKUP is probably the most used—and most misused—function in Excel. I've reviewed thousands of spreadsheets, and the same mistakes appear again and again. Let's fix them once and for all.

The Problem

Your VLOOKUP returns:

• #N/A when you KNOW the value exists
• Wrong values that don't match your lookup
• Errors after you insert or delete columns
• Different results than you expected

Sound familiar? You're not alone.

The Solution

Understanding these common mistakes will transform your VLOOKUPs from frustrating to reliable. Let's diagnose and fix each issue.

What You'll Need

• Any version of Excel
• A lookup scenario to practice with
• Willingness to unlearn bad habits

Mistake #1: Using TRUE Instead of FALSE (The Silent Killer)

The Mistake:

Why It's Wrong: The fourth argument (TRUE or omitting it) tells Excel to find an "approximate match." This is designed for sorted numerical ranges like tax brackets. For most lookups, you want exact matches.

What Happens: Excel finds the largest value LESS THAN OR EQUAL TO your lookup value. If your data isn't sorted, you get unpredictable, wrong results.

The Fix:

ALWAYS use FALSE (or 0) for exact matches. This is the fix for 80% of VLOOKUP problems.

Rule: Unless you're looking up ranges (tax brackets, grade scales), always use FALSE.

Mistake #2: Extra Spaces in Your Data

The Mistake: Your lookup value is "John Smith" but your data contains "John Smith " (with trailing space).

Why It's Wrong: Excel sees these as different values. Your VLOOKUP returns #N/A even though you can "see" the match.

How to Detect:

If "John Smith" shows 11 characters but should be 10, you have hidden spaces.

The Fix: Clean your data with TRIM:

Or clean the entire column first:

1. Insert helper column
2. Use =TRIM(A2) and copy down
3. Copy helper column, Paste Special > Values over original
4. Delete helper column

Mistake #3: Hardcoded Column Numbers

The Mistake:

Why It's Wrong: If someone inserts a column between B and F, your formula still returns column 3—but that's now a different column than intended.

The Fix: Use MATCH to dynamically find the column:

Or better yet, use INDEX-MATCH:

This explicitly references the return column and never breaks.

Mistake #4: Looking Up in the Wrong Column

The Mistake: Your table has columns in this order: Name | ID | Department | Salary

You write:

Expecting to find F2 in the ID column... but VLOOKUP always searches the FIRST column of your range (Names, not IDs).

Why It's Wrong: VLOOKUP only looks in the leftmost column of your table_array.

The Fix: Adjust your range to start with the lookup column:

Or use INDEX-MATCH which can look up in any column:

Mistake #5: Mixing Data Types

The Mistake: Looking up "123" (text) in a column of 123 (numbers), or vice versa.

Why It's Wrong: Excel treats text-123 and number-123 as completely different values.

How to Detect: Numbers align right; text aligns left (by default). Also:

The Fix: Convert to the same type:

Text to Number:

Number to Text:

Or fix the source data:

• Text to Numbers: Select cells > Data > Text to Columns > Finish
• Numbers to Text: Use TEXT function or Format Cells

Mistake #6: Searching Entire Columns (Performance Issue)

The Mistake:

Why It's Problem: A:C means searching 1,048,576 rows. Your workbook becomes slow, especially with multiple VLOOKUPs.

The Fix: Limit your range to actual data:

Or use a Table reference that auto-adjusts:

Mistake #7: No Error Handling

The Mistake:

Why It's Wrong: When A2 isn't found, you get an ugly #N/A error. In reports, this looks unprofessional.

The Fix: Wrap in IFERROR:

Or use IFNA for more targeted error handling:

IFNA only catches #N/A, letting other errors (like #REF!) show—which might indicate a real problem.

The Complete Solution

Here's a robust VLOOKUP that avoids all common mistakes:

=IFERROR(
    VLOOKUP(
        TRIM(A2),
        Table1,
        MATCH("Price", Table1[#Headers], 0),
        FALSE
    ),
    "Not Found"
)

Formula Breakdown

Common Variations

Variation 1: Case-Sensitive VLOOKUP

VLOOKUP is case-insensitive by default. For case-sensitive:

Variation 2: Wildcard VLOOKUP

Find partial matches:

The asterisks match any characters before/after your search term.

Variation 3: Multiple Criteria VLOOKUP

Create a helper column concatenating your criteria:

Where column D in Data contains concatenated criteria.

Pro Tips

• Test with known values: Before trusting a VLOOKUP, test it with a value you can manually verify
• Check your #N/A errors: Each one is telling you something—investigate before hiding with IFERROR
• Consider XLOOKUP: If you have Excel 365 or 2021, XLOOKUP fixes most of these issues by design
• Document complex lookups: Add a comment explaining what the formula does

Troubleshooting Checklist

When your VLOOKUP isn't working, check:

When to Abandon VLOOKUP

Consider switching to INDEX-MATCH or XLOOKUP when:

• You need to look left (VLOOKUP can only return columns to the right)
• Column positions change frequently
• You need multiple criteria matching
• You're working with very large datasets (INDEX-MATCH is faster)
• You have Excel 365/2021 (XLOOKUP is simply better)

Conclusion

VLOOKUP isn't broken—it's just misunderstood. The function works exactly as designed; the problem is that its design has quirks that catch users off guard. By understanding these common mistakes, you can write VLOOKUPs that work reliably every time.

Remember: FALSE for exact match, TRIM for clean data, MATCH for dynamic columns, and IFERROR for graceful failures. Master these principles, and VLOOKUP will become a trustworthy tool instead of a source of frustration.

Now go fix those broken VLOOKUPs!