Advanced VLOOKUP Techniques

Master advanced VLOOKUP techniques that separate beginners from power users.

Problem 1: Looking Left (VLOOKUP Can't!)

VLOOKUP limitation: Only searches first column and returns values to the right.

Solution: INDEX-MATCH

=INDEX(A2:A10, MATCH("Apple", B2:B10, 0))

Works in any direction!

Problem 2: Approximate Match

Use TRUE for tiered data:

• Tax brackets
• Commission tiers
• Shipping rates

Example:

=VLOOKUP(75000, A2:B5, 2, TRUE)

CRITICAL: Data must be sorted ascending!

Problem 3: Wildcard VLOOKUP

Find partial matches:

=VLOOKUP("*iPhone*", A2:B10, 2, FALSE)

Wildcards:

• * = any number of characters
• ? = single character

Problem 4: Multiple Criteria

Solution: Helper Column

Column D: =A2&B2 (combine criteria)

Then:

=VLOOKUP("John"&"Sales", D2:E10, 2, FALSE)

Problem 5: Handle #N/A Errors

Use IFERROR:

=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Not Found")

Problem 6: Dynamic Column Index

Instead of hardcoding column number:

=VLOOKUP(A2, Data!A:Z, MATCH("Price", Data!A1:Z1, 0), FALSE)

Auto-adjusts when columns change!

Problem 7: Search Multiple Sheets

Nested IFERROR:

=IFERROR(VLOOKUP(A2, Sheet1!A:B, 2, 0),
  IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0),
    VLOOKUP(A2, Sheet3!A:B, 2, 0)))

Checks Sheet1, then Sheet2, then Sheet3.

Common Mistakes

1. Extra Spaces "Apple" ≠ "Apple "

Fix: =VLOOKUP(TRIM(A2), Data, 2, 0)

2. Number vs Text
"123" ≠ 123

Fix: =VLOOKUP(VALUE(A2), Data, 2, 0)

3. Wrong Range Lock

=VLOOKUP(A2, $B$2:$C$10, 2, FALSE)  ✅

When to Use Alternatives

• INDEX-MATCH: Need to look left, large datasets
• XLOOKUP: You have Excel 365
• VLOOKUP: Simple right lookups, small data

You're now a VLOOKUP expert! 🎓