📊 Excel Basics #21 – XLOOKUP() Function

The "XLOOKUP()" function is the modern replacement for both "VLOOKUP()" and "HLOOKUP()". It is more flexible, easier to use, and solves many of the limitations of older lookup functions.

«Note: "XLOOKUP()" is available in Microsoft 365 and Excel 2021+. It is not available in Excel 2019 or earlier.»

📌 What is the XLOOKUP() Function?
"XLOOKUP()" searches for a value in one range and returns the corresponding value from another range.

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

Unlike "VLOOKUP()", you don't need to specify a column number.

📌 Example 1 – Find Employee Department
ID | Name | Department
101 | Rahul | IT
102 | Priya | HR
103 | Amit | Finance
104 | Neha | Marketing

Formula:
=XLOOKUP(103,A2:A5,C2:C5)

Result: Finance

📌 Example 2 – Using a Cell Reference

If cell E2 contains an Employee ID:

=XLOOKUP(E2,A2:A5,C2:C5,"Employee Not Found")

If the ID exists, Excel returns the department.
If it doesn't exist, Excel displays: Employee Not Found

📌 Why XLOOKUP() is Better than VLOOKUP()
✅ Looks up values from left to right and right to left
✅ No need to count column numbers
✅ Built-in if_not_found argument
✅ Works with both vertical and horizontal data
✅ More reliable when columns are inserted or deleted

📌 VLOOKUP vs XLOOKUP()
VLOOKUP()
• Searches only left to right
• Uses column index numbers
• Requires IFERROR() to handle missing values

XLOOKUP()
• Searches in any direction
• Uses lookup and return ranges
• Has built-in error handling
• Easier to read and maintain

📌 Real-World Uses
• Find employee information
• Retrieve product prices
• Match customer records
• Search invoice details
• Build interactive dashboards

📌 Common Mistakes
• Using lookup and return arrays of different sizes
• Trying to use "XLOOKUP()" in older Excel versions
• Referencing the wrong lookup range

✅ Best Practices
• Use "XLOOKUP()" instead of "VLOOKUP()" whenever available
• Use the "if_not_found" argument to display meaningful messages
• Keep the lookup and return arrays the same size
• Use structured table references for dynamic formulas

💡 Bonus Example – Return Multiple Columns
=XLOOKUP(E2,A2:A5,B2:C5)
If supported by your Excel version, this returns both the Name and Department for the matching Employee ID.

"XLOOKUP()" is one of the most valuable Excel functions for modern data analysis and is becoming the preferred lookup function across industries.

Double Tap ❤️ For More