📌 Introduction
If you have ever used VLOOKUP in Excel, you know the frustration. It can only search from left to right, it breaks when you insert a column, and it always picks the first match — even when you need something else. For years, Indian professionals using Excel for MIS reports, salary sheets, and GST data have struggled with these VLOOKUP limitations.
Microsoft heard the complaints. In 2019, they introduced a brand new function called XLOOKUP — and it solves almost every problem VLOOKUP has. It is more powerful, easier to write, and far more flexible. Whether you are a beginner or a working professional, XLOOKUP is the lookup formula you should be learning in 2026.
In this post, you will learn exactly what XLOOKUP is, how to use it with real Indian examples, the step-by-step process, common mistakes to avoid, and pro tips to use it like an expert.
📌 What is the XLOOKUP Function?
XLOOKUP is a lookup function in Excel that searches for a value in a range or array and returns the corresponding value from another range or array. Think of it like a smart search engine inside Excel.
Real Indian Example: Rahul works in the HR department of a company in Pune. He has a list of 500 employee IDs and their salaries in one sheet. Every month, his manager gives him a list of 50 employee IDs and asks him to fetch their salaries. Instead of searching manually, Rahul uses XLOOKUP — he types the employee ID, and Excel instantly returns the salary from the data sheet. What used to take 30 minutes now takes 30 seconds.
XLOOKUP is available in Excel 365, Excel 2021, and Excel for the web. If you are using Excel 2019 or older, you will need to use INDEX+MATCH instead. Check your version: File → Account → About Excel.
📌 Syntax of XLOOKUP
Parameter Explanation
| Parameter | Required? | What it means |
|---|---|---|
| lookup_value | ✔ Yes | The value you want to search for (e.g. Employee ID, Product Code) |
| lookup_array | ✔ Yes | The column or row to search in (where your IDs or codes are listed) |
| return_array | ✔ Yes | The column to return the result from (e.g. Salary, City, Department) |
| [if_not_found] | Optional | What to show if no match is found — e.g. “Not Found” instead of #N/A error |
| [match_mode] | Optional | 0 = exact match (default), -1 = next smaller, 1 = next larger, 2 = wildcard |
| [search_mode] | Optional | 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending |
📌 XLOOKUP Example with Indian Data
Priya is an accountant at a Delhi-based trading company. She has an employee database in Sheet1 and needs to fetch the Department and Salary for specific employees in her MIS report.
Employee Database (Sheet1 — A1:D8)
| Emp ID | Employee Name | Department | Salary (₹) |
|---|---|---|---|
| EMP001 | Rahul Sharma | Sales | ₹45,000 |
| EMP002 | Priya Mehta | Accounts | ₹52,000 |
| EMP003 | Amit Joshi | IT | ₹68,000 |
| EMP004 | Suresh Kumar | HR | ₹38,000 |
| EMP005 | Neha Verma | Marketing | ₹41,000 |
| EMP006 | Vikram Singh | Operations | ₹55,000 |
XLOOKUP Formulas and Results
| Task | Formula | Result |
|---|---|---|
| Find the salary of EMP003 | =XLOOKUP("EMP003",A2:A7,D2:D7) | ₹68,000 |
| Find the department of EMP005 | =XLOOKUP("EMP005",A2:A7,C2:C7) | Marketing |
| Search with a custom error message | =XLOOKUP("EMP009",A2:A7,D2:D7,"Not Found") | Not Found |
| Find name using cell reference | =XLOOKUP(G2,A2:A7,B2:B7,"Not Found") | Rahul Sharma |
📌 Step-by-Step: How to Use XLOOKUP
Follow these steps to write your first XLOOKUP formula in Excel:
Set up your data table
Make sure your data has a unique ID column (like Employee ID or Product Code) and the value you want to return (like Salary or Price) in any column — left or right of the ID column.
Click on the cell where you want the result
For example, if you want to show Amit’s salary in cell H2, click on H2.
Type the formula
Type =XLOOKUP( — Excel will show you a tooltip with the parameters to fill in.
Fill the three required arguments
First: the value to look for (or the cell containing it). Second: the column to search in. Third: the column to return from. Separate each with a comma.
Add the “if_not_found” argument
Always add a fourth argument like "Not Found" to avoid ugly #N/A errors when an ID is not in your data.
Press Enter and drag down
Press Enter to see the result. Then click on the result cell and drag the formula down to apply it to all rows in your list.
When dragging the formula down, press F4 on the lookup_array and return_array to add dollar signs — e.g.
$A$2:$A$100. This prevents the range from shifting when you copy the formula down.📌 When Should You Use XLOOKUP?
- Fetching employee salary, department, or designation from HR master data in Tally or SAP exports
- Looking up GST rates or HSN codes for products in a tax invoice sheet
- Pulling customer details (city, credit limit, outstanding balance) from a customer master list
- Building MIS reports where you need to merge data from two different Excel sheets
- Fetching product price or stock quantity from an inventory list for quotation preparation
- Replacing your existing VLOOKUP formulas that break when new columns are inserted
- Searching from right to left — which VLOOKUP cannot do at all
📌 Common Mistakes Beginners Make
| Mistake | What Goes Wrong | How to Fix It |
|---|---|---|
| Different array sizes | Returns #VALUE! error | Make sure lookup_array and return_array have the same number of rows (e.g., both A2:A100 and D2:D100) |
| Not locking array references | The formula gives wrong results when dragged | Press F4 to add $ signs: $A$2:$A$100 |
| Extra spaces in data | Returns “Not Found” even when the value exists | Use TRIM: wrap the lookup value in TRIM() Or clean your data first |
| Using an older Excel version | Returns #NAME? error | XLOOKUP needs Excel 365 or 2021. Use INDEX+MATCH for older versions |
| No if_not_found argument | Shows ugly #N/A error in report | Always add 4th argument: "Not Found" or "" |
XLOOKUP is not case-sensitive by default. “emp001” and “EMP001” will match each other. If you need case-sensitive lookup (e.g., passwords or product codes with mixed case), use EXACT() inside the formula with the match_mode set to 2 (wildcard).
📌 Pro Tips for XLOOKUP
Use a multi-column return_array B2:D100 to return Name, Dept, and Salary all at once with a single formula — impossible with VLOOKUP!
Set search_mode to -1 to find the last occurrence. Perfect for finding the most recent transaction date for a customer in your Tally export.
Use XLOOKUP inside XLOOKUP to do a two-way lookup — find a value at the intersection of a row and column. Ideal for pricing matrices with different products and cities.
Set match_mode to 2 and use * wildcard: "Rahul*" will match “Rahul Sharma”, “Rahul Mehta” — great for partial name lookups.
📌 XLOOKUP vs VLOOKUP — Quick Comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search direction | Left to right only | Any direction ✔ |
| Column index needed? | Yes (error-prone) | No — just select the column ✔ |
| Breaks when column is inserted? | Yes ✘ | No ✔ |
| Custom error message | Needs IFERROR wrapper | Built-in 4th argument ✔ |
| Return multiple columns | No ✘ | Yes ✔ |
| Find the last match | No ✘ | Yes (search_mode -1) ✔ |
🎯 Conclusion
XLOOKUP is, without doubt, the best lookup function in Excel today. It is simpler to write than VLOOKUP, more powerful, and much more reliable. Whether you are building an employee salary report, a GST invoice, an MIS dashboard, or a product price list, XLOOKUP will make your work faster and error-free.
Key things to remember:
Always add “if_not_found.”
Lock arrays with F4
Works left, right, any direction
Returns multiple columns
Needs Excel 365 or 2021
Download the free practice workbook, try all 5 exercises, and you will master XLOOKUP in one sitting! If you found this helpful, please share it with your colleagues. Leave any questions in the comments below. Happy learning! 😊
