Function: XLOOKUP()

The XLOOKUP function is available in Microsoft 365, Excel 2021, and Excel 2024. It is not supported in older versions like Excel 2019 and earlier.

🧭 Purpose:

The XLOOKUP() function is an advanced replacement for both VLOOKUP() and HLOOKUP().
It searches for a value in a column or row and returns a matching value from another column or row — in any direction (left, right, up, or down).

It also handles errors gracefully and doesn’t need column numbers like VLOOKUP.

🧮 Syntax:

Parameters:

📘 Explanation:

Unlike VLOOKUP, which only looks rightward,
XLOOKUP can look left, right, up, or down — and it doesn’t break if you insert new columns.

Example:

✅ Finds “S103” in column A and returns the corresponding value from column C.

🧑‍🎓 Example 1: Student Course Lookup

Formula:

✅ Result → Tally GST

🎯 Simple, readable, and dynamic — no column number needed!

📦 Example 2: Reverse Lookup (Left Lookup)

You want to find Product Name based on Code — i.e., lookup to the left!

Formula:

✅ Result → Laptop

💡 VLOOKUP can’t do this easily, but XLOOKUP can.

⚙️ Why XLOOKUP is Better:

🧩 Project Tasks for Students

🧠 Project 1 (Basic): Student Info Fetcher

🎯 Task:
Enter Student ID in cell G2, and display:

• Name

• Course

• Fees

Formula example:

✅ Result: Name of Student

💡 Use 3 formulas to fetch all 3 fields (Name, Course, Fees).

🏪 Project 2 (Intermediate): Store Product Lookup

🎯 Task:
Create a Product Finder Tool:

• User inputs Product Code

• Displays Product Name, Brand, Price, and Stock

Formula:

✅ You can use multiple XLOOKUPs side by side to create a complete info card.

💼 Project 3 (Hard): Employee Performance Dashboard

🎯 Task:

1. User inputs Employee ID (e.g., “E103”) and Year (e.g., “2024”).

2. Excel shows the Rating for that year dynamically.

Formula:

✅ Nested XLOOKUP — works like a 2D lookup (both ID and Year).

💡 Great for dashboards where year selection changes automatically.

🧮 Project 4 (Hard): Banking Interest & EMI Calculator

🎯 Task:
Create a form:

• User enters Loan Code (e.g., “L103”)

• Enters Year (e.g., “2025”)

• Excel returns Interest Rate

Formula:

✅ Output → 11.2

💡 Ideal for finance projects or dashboards.

🏦 Project 5 (Hard): Dynamic Admission Fee System

🎯 Task:

1. User inputs Course Name in a dropdown.

2. Excel returns Fees, Discount, and Final Payable using XLOOKUP.

3. Add logic for “Not Found” courses.

Formula examples:

✅ Gives fully dynamic results for any selected course.

💡 Extend this with Data Validation and conditional formatting to build a working fee calculator system.

✅ Key Tip:
Always use IFERROR() or if_not_found inside XLOOKUP to keep dashboards clean: