Mastering XLOOKUP in Microsoft Excel

---

When Spreadsheets Fight Back

Excel wizards begged for it for years:

• A function that doesn’t break when you insert a column.

• A function that can look left and right.

• A function that doesn’t force you into weird hacks just to return “Not Found.”

Microsoft finally listened. Enter XLOOKUP — the shiny new spell that replaced both VLOOKUP and INDEX-MATCH for many Excel users. If you’re on Excel 2019+ or Microsoft 365, this is the lookup spell you’ll want to master.

---

The Basics (Definition & Syntax)

XLOOKUP finds a value in a range and returns its match from another range.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value → What you’re looking for.

• lookup_array → Where to look.

• return_array → Where to pull results from.

• if_not_found → Optional custom message (no more IFERROR wrapper needed).

• match_mode → Exact, approximate, wildcards, or exact match with case.

• search_mode → Search first-to-last or last-to-first.

👉 In plain English:

“Find me this value, wherever it is, and return the matching one — even if I look left or right.”

---

3. Basic Example

Dataset:

ID	Name	Score
101	Alice	92
102	Bob	85
103	Charlie	78

Find Bob’s score:

=XLOOKUP("Bob", B2:B4, C2:C4, "Not Found")

Result → 85 🎉

---

Real-World Quirks (The Hidden Traps)

Even modern spells have quirks:

1. Not available in older Excel versions → You’ll still need INDEX-MATCH for legacy files.

2. Default is exact match → Safer than VLOOKUP, but may confuse old-school users.

3. Performance → On very large arrays, multiple XLOOKUPs can still slow things down.

4. Dynamic arrays → Spill behavior can surprise users if they’re not careful.

---

Fixes & Workarounds

• Backwards Compatibility

  Save as .xlsx but warn older users — XLOOKUP will break on their versions. Provide an INDEX-MATCH fallback if needed.

• Large Data Performance

  Restrict ranges, or use helper columns instead of hundreds of XLOOKUPs.

• Custom Error Handling

  Built-in:
  =XLOOKUP(“David”, B2:B4, C2:C4, “Not Found”)

---

Tricky Cases from the Forums (The Hidden Spells)

1. Search from the Bottom (Last Match)
   =XLOOKUP(“Bob”, B2:B10, C2:C10, “Not Found”, 0, -1)

2. Wildcard Matching
   =XLOOKUP(“Cha*”, B2:B10, C2:C10, “Not Found”, 2)

3. Two-Way Lookup (Row + Column)
   =XLOOKUP(“Alice”, A2:A4, XLOOKUP(“Math”, B1:D1, B2:D4))

4. Multiple Criteria (Nested XLOOKUPs)

   Combine fields (e.g., ID & Name) into one lookup column, then use XLOOKUP.

---

For Ancient Versions of Excel (Pre-2019 Users)

If your Excel doesn’t support XLOOKUP:

• Use INDEX-MATCH:
  =IFERROR(INDEX(C2:C10, MATCH(“Bob”, B2:B10, 0)), “Not Found”)

• Use VLOOKUP (with limitations):
  =IFERROR(VLOOKUP(“Bob”, B2:C10, 2, 0), “Not Found”)

---

Pro Tips (Beyond the Docs)

Forbidden Tricks They Don’t Teach in Class

• Dynamic Range Return

  Return an entire row of data in one go:
  =XLOOKUP(“Alice”, A2:A10, B2:D10)

• → Spills out all of Alice’s details.

• Nested XLOOKUPs for Complex Tables

  Combine multiple lookups into one formula for dashboard-like behavior.

• Case-Sensitive Lookup

  Wrap with EXACT inside FILTER for extra control.

---

Problems → Quick Fixes at a Glance

Problem	Formula Fix
Need last match	=XLOOKUP(val, array, return, ,0,-1)
Wildcard lookup	=XLOOKUP(“Cha*”, names, scores, ,2)
Missing value	Use 4th argument → “Not Found”
Two-way lookup	Nested XLOOKUP across row + column
Legacy Excel users	Fall back to INDEX-MATCH

---

The End of This Chapter

XLOOKUP is the lookup spell we always wanted — flexible, reliable, and powerful. It fixes the weaknesses of VLOOKUP and INDEX-MATCH, and adds new tricks of its own.

But Excel magic doesn’t stop here. The real fun begins when you start combining lookup functions with FILTER, SORT, and dynamic arrays.

Next up in the Mastering Excel series:

👉 Mastering FILTER: Summoning Multiple Results with One Spell