Excel XLOOKUP Mastery — The Evolution of VLOOKUP!

In Excel, commonly used functions for searching and linking data include VLOOKUP, HLOOKUP, INDEX, and MATCH. However, since 2020, a powerful and flexible function called XLOOKUP has been introduced, addressing the shortcomings of its predecessors.

This article walks you through the basics and practical use cases of XLOOKUP!

---

🔍 What is XLOOKUP?

XLOOKUP is a modern Excel function that allows you to search for values both vertically and horizontally, accurately locating the desired result. It’s available in Microsoft 365 and Excel 2021 or later.

---

📌 Basic Syntax:

excel

복사편집

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

• lookup_value: The value to search for
• lookup_array: The range to search within (e.g., list of IDs)
• return_array: The range to return values from (e.g., list of names)
• if_not_found (optional): Value to display if not found
• match_mode (optional): Matching type (0: exact, -1: less than, 1: greater than, 2: wildcard)
• search_mode (optional): Search direction (1: first-to-last, -1: last-to-first)

---

🎯 Comparison: XLOOKUP vs VLOOKUP

FeatureVLOOKUPXLOOKUP

Search direction	Right only	Bi-directional
Column index required	Yes	No
If not found	Error (#N/A)	Custom value
Needs sorting	Yes (approximate)	No
Array return	Not supported	Supported (multi-dimensional)

 

---

🎨 Color Legend:

• 🔴 Red = Value to look up
• 🟢 Green = Search range
• 🔵 Blue = Return value column

About F4: It locks rows/columns with the $ sign.

• $I3 → Column I is fixed, row 3 can change.
• Copying from J3 to J4 will change $I3 to $I4.
• Copying from I3 to H3 keeps $I3, meaning the column remains locked.

---

🔁 Searching from Bottom Up

By default, XLOOKUP searches from top to bottom.
To search bottom-up, use:

excel

=XLOOKUP($I3,$D:$D,$E:$E,,,-1)

---

✅ Multi-condition XLOOKUP

When values like A~D are mixed, and you need multiple criteria, try:

=XLOOKUP(1,($D$3:$D$9=$I3)*($C$3:$C$9=$H3),$E$3:$E$9)

How it works:

• ($D$3:$D$9=I3) → Returns TRUE/FALSE array for code match
• ($C$3:$C$9=H3) → Returns TRUE/FALSE array for gender match
• Multiplying arrays = AND condition (TRUE × TRUE = 1)
• XLOOKUP(1, …) finds the first match where both conditions are true

Note: 1 = TRUE, 0 = FALSE, and + acts like an OR condition

---

If anything is unclear, feel free to leave a comment!