Excel Update: XLOOKUP Magic Is Transforming Daily Reporting Tasks

XLOOKUP has officially replaced VLOOKUP and INDEX–MATCH for millions of Excel users. It is faster, cleaner, and supports powerful features like multiple column outputs and non-continuous lookup using CHOOSE.

---

In this guide, we use a dataset named Empdata (Converted data into Table and named it as Empdata), containing the following fields:

Download Practice File

EmpID | Name | Department | Salary | City | Age | JoiningDate | Email | Phone | Manager

Below are three real-life lookup cases based on your practice data.

Watch Video and Practice Now

---

XLOOKUP in a Single Column

Lookup Values Used:

EmpID:

• 1002
• 1004
• 1006

Find Name for each EmpID.

Formula:

=XLOOKUP(A3, Empdata[EmpID], Empdata[Name])

Explanation:

This formula searches EmpID from the Empdata table and returns the corresponding Name. If any ID is missing, XLOOKUP can return an error or a custom message.

Read Also: I Finally Mastered Excel Shortcuts: 35+ Time-Saving Excel Keyboard Tricks I Use Daily

XLOOKUP in Multiple Columns

Lookup Values Used:

EmpID:

• 1002
• 1004
• 1005
• 1007
• 1003

Goal:

Fetch multiple details: Name, Department, Salary, City, Age, JoiningDate, Email

=XLOOKUP(A9, Empdata[EmpID], Empdata[[Name]:[Email]])

Explanation:

With one XLOOKUP, Excel returns 7 columns of information at once.
The result spills horizontally across the sheet.

Why It’s Useful:

1. Single formula → multiple outputs
2. Perfect for HR records, MIS dashboards, employee verification
3. Auto-updates when new data is added to the Empdata table

XLOOKUP in Non-Continuous Columns

Lookup Values Used:

EmpID:

• 1002
• 1004
• 2050

Goal:

Fetch Name, Salary, Department, City These columns are not next to each other in the table.

---

Formula:

=XLOOKUP(A16, Empdata[EmpID],
CHOOSE({1,2,3,4}, Empdata[Name], Empdata[Salary], Empdata[Department], Empdata[City]))

Explanation:

• CHOOSE creates a virtual array using non-adjacent fields
• XLOOKUP returns all 4 values at once

Why XLOOKUP Is Now Essential for Excel Users

✔ Supports multiple columns in one formula

✔ Works with dynamic tables

✔ Can extract non-continuous fields

✔ Cleaner and more powerful than VLOOKUP

Thanks to these advantages, XLOOKUP is now the most recommended lookup function in Excel.