Introduction
XLOOKUP is a modern Excel lookup function that helps you find information from a selected range. You can search for an employee ID, product name, student roll number, or sales record and return the related information. It is more flexible than traditional lookup functions because it can search in any direction and handle missing values directly.
What Is the XLOOKUP Function in Excel?
The XLOOKUP function searches for a specific value in one range and returns the corresponding value from another range.
For example, suppose a company maintains employee records containing Employee ID, Name, Department, and Salary.
If you enter an Employee ID, XLOOKUP can find that ID and return the employee’s salary.
In simple words:
- Excel searches for the required value in the lookup range.
- It identifies the position where the value is found.
- It checks the corresponding position in the return range.
- It returns the related information.
Important: Unlike VLOOKUP, XLOOKUP does not require the lookup column to be the first column of the entire table. You can select the lookup range and return range separately.
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Understanding the Arguments
| Argument | Meaning |
|---|---|
| lookup_value | The value you want Excel to search for. |
| lookup_array | The range where Excel should search for the value. |
| return_array | The range containing the result you want to retrieve. |
| if_not_found | The message or value Excel should return when no match is found. |
| match_mode | Specifies whether you want an exact match, approximate match, or wildcard match. |
| search_mode | Specifies the direction or method Excel should use when searching. |
Understanding Each Argument
1. lookup_value
This is the value you want to find.
For example, if you want to search for employee ID E103, then E103 is your lookup_value.
2. lookup_array
This is the range where Excel searches for the lookup value.
For example, if Employee IDs are stored in A2, then A2 is your lookup_array.
3. return_array
This is the range from which Excel returns the required information.
For example, if employee salaries are stored in D2, then D2 is your return_array.
4. if_not_found
This argument specifies what Excel should display when it cannot find the lookup value.
For example:
=XLOOKUP("E110",A2:A6,D2:D6,"Employee Not Found")
If E110 does not exist in the selected range, Excel displays Employee Not Found instead of the #N/A error.
5. match_mode
This argument controls how Excel matches the lookup value.
| Match Mode | Meaning |
|---|---|
| 0 | Exact match. This is the default setting. |
| -1 | Exact match or the next smaller value. |
| 1 | Exact match or the next larger value. |
| 2 | Wildcard match using *, ?, and ~. |
6. search_mode
This argument controls the direction or method of searching.
| Search Mode | Meaning |
|---|---|
| 1 | Search from first to last. This is the default. |
| -1 | Search from last to first. |
| 2 | Binary search on data sorted in ascending order. |
| -2 | Binary search on data sorted in descending order. |
For beginners, the default settings are usually sufficient. You can use the other options when a particular lookup situation requires them.
How Does the XLOOKUP Function Work?
Suppose a company maintains the following employee records:
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
The HR manager wants to retrieve the salary of employee E103.
Use this formula:
=XLOOKUP("E103",A2:A6,D2:D6)
Let’s understand how Excel evaluates the formula:
"E103"is the Employee ID Excel needs to find.A2:A6contains the Employee IDs. Excel searches this range.D2:D6contains the salaries. Excel will return the value from this range.- Excel finds E103 in the third position of the lookup range.
- It retrieves the value from the third position of the return range.
Expected Result: 45000
The result is 45000 because employee E103 has a salary of 45000.
Practical Examples of XLOOKUP Function in Excel
Let’s understand XLOOKUP through different business and educational situations.
Question 1: Find an Employee’s Salary Using Employee ID
Problem Statement
An HR department maintains employee records. The manager wants to enter an Employee ID and retrieve the employee’s salary.
Data Table
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| E101 | Rahul | IT | 35000 |
| E102 | Priya | HR | 40000 |
| E103 | Aman | Sales | 45000 |
| E104 | Neha | Finance | 50000 |
| E105 | Karan | IT | 55000 |
Assume cell F2 contains E103.
Excel Formula
=XLOOKUP(F2,A2:A6,D2:D6)
Expected Result
| Employee ID | Salary |
|---|---|
| E103 | 45000 |
Explanation
F2contains the Employee ID to search for.A2:A6is the lookup range containing Employee IDs.D2:D6is the return range containing salaries.- Excel finds E103 and returns the salary from the corresponding position.
Concept Covered
Using XLOOKUP with a cell reference to retrieve employee information.
Question 2: Find an Employee’s Name from the Department Column
Problem Statement
A company maintains employee records. The manager wants to search for an employee’s department and retrieve the corresponding employee name.
This example demonstrates that XLOOKUP can search in a column and return information from a column located to its left.
Data Table
| Employee ID | Employee Name | Department | City |
|---|---|---|---|
| E201 | Arjun | IT | Delhi |
| E202 | Meera | HR | Noida |
| E203 | Dev | Sales | Gurgaon |
| E204 | Pooja | Finance | Delhi |
| E205 | Rohan | Marketing | Noida |
Excel Formula
=XLOOKUP("Sales",C2:C6,B2:B6)
Expected Result
| Department | Employee Name |
|---|---|
| Sales | Dev |
Explanation
"Sales"is the department Excel needs to find.C2:C6contains department names.B2:B6contains employee names.- Excel finds Sales and returns Dev from the corresponding position.
Concept Covered
XLOOKUP can return information from the left side of the lookup range. This is a major advantage over traditional VLOOKUP.
Question 3: Display a Custom Message When a Value Is Not Found
Problem Statement
An electronics store maintains product information. The manager wants to search for a Product ID and retrieve its price.
If the Product ID does not exist, the formula should display Product Not Found.
Data Table
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| P101 | Laptop | Electronics | 55000 |
| P102 | Keyboard | Accessories | 1200 |
| P103 | Mouse | Accessories | 600 |
| P104 | Monitor | Electronics | 12000 |
| P105 | Printer | Electronics | 8500 |
Assume cell F2 contains P110.
Excel Formula
=XLOOKUP(F2,A2:A6,D2:D6,"Product Not Found")
Expected Result
| Search Product ID | Result |
|---|---|
| P110 | Product Not Found |
Explanation
F2contains the Product ID to search for.A2:A6is the lookup range.D2:D6contains product prices."Product Not Found"is the custom message.- Since P110 does not exist in the lookup range, Excel displays Product Not Found.
Concept Covered
Using the if_not_found argument to display a meaningful message instead of the default #N/A error.
Question 4: Retrieve a Student’s Subject Marks
Problem Statement
A school maintains student marks in different subjects. The teacher wants to retrieve the Science marks of a student using the student’s Roll Number.
Data Table
| Roll Number | Student Name | Mathematics | English | Science |
|---|---|---|---|---|
| 101 | Aarav | 85 | 78 | 90 |
| 102 | Riya | 92 | 88 | 95 |
| 103 | Mohit | 75 | 82 | 80 |
| 104 | Simran | 89 | 91 | 87 |
| 105 | Kabir | 80 | 76 | 84 |
Assume cell G2 contains 104.
Excel Formula
=XLOOKUP(G2,A2:A6,E2:E6)
Expected Result
| Roll Number | Science Marks |
|---|---|
| 104 | 87 |
Explanation
G2contains the Roll Number.A2:A6contains student Roll Numbers.E2:E6contains Science marks.- Excel finds Roll Number 104 and returns 87 from the corresponding position.
Concept Covered
Retrieving student marks using a unique Roll Number.
Question 5: Find an Incentive Using Approximate Matching
Problem Statement
A company provides incentives based on employee sales. The incentive percentage depends on the sales amount.
The manager wants to find the incentive percentage for an employee who has achieved sales of 35000.
Data Table
| Minimum Sales | Incentive |
|---|---|
| 0 | 2% |
| 10000 | 3% |
| 20000 | 5% |
| 30000 | 7% |
| 40000 | 10% |
Excel Formula
=XLOOKUP(35000,A2:A6,B2:B6,,-1)
Expected Result
| Sales Amount | Incentive |
|---|---|
| 35000 | 7% |
Explanation
35000is the sales amount to search for.A2:A6contains minimum sales values.B2:B6contains incentive percentages.- The fourth argument is left empty because no custom message is required.
-1tells Excel to find an exact match or the next smaller value.- Excel finds 30000 as the largest minimum sales value not exceeding 35000 and returns 7%.
Concept Covered
Using match_mode -1 to find the next smaller value when an exact match is unavailable.
Question 6: Search for a Product Using a Wildcard
Problem Statement
An electronics store maintains product names. The manager remembers that a product name starts with Laptop but does not remember its complete name.
The manager wants to find the price of the first matching product.
Data Table
| Product Name | Price |
|---|---|
| Laptop Dell | 55000 |
| Keyboard Logitech | 1200 |
| Laptop HP | 48000 |
| Mouse Wireless | 600 |
| Monitor Samsung | 12000 |
Excel Formula
=XLOOKUP("Laptop*",A2:A6,B2:B6,,2)
Expected Result
| Matching Product | Price |
|---|---|
| Laptop Dell | 55000 |
Explanation
"Laptop*"is the lookup value. The asterisk means any number of characters can follow Laptop.A2:A6contains product names.B2:B6contains product prices.- The fourth argument is left empty because no custom message is required.
2specifies wildcard matching.- Excel finds the first product name beginning with Laptop and returns 55000.
Concept Covered
Using match_mode 2 for wildcard searches.
Wildcard symbols:
| Symbol | Meaning |
|---|---|
* | Matches any number of characters. |
? | Matches exactly one character. |
~ | Searches for an actual * or ? character. |
Question 7: Find the Latest Matching Record
Problem Statement
A company maintains multiple sales records for the same product. The manager wants to retrieve the latest recorded sales amount for Laptop.
Data Table
| Date | Product | Sales |
|---|---|---|
| 01-Jan-2026 | Laptop | 50000 |
| 05-Jan-2026 | Mobile | 30000 |
| 10-Jan-2026 | Laptop | 60000 |
| 15-Jan-2026 | Tablet | 25000 |
| 20-Jan-2026 | Laptop | 75000 |
Excel Formula
=XLOOKUP("Laptop",B2:B6,C2:C6,-1)
Expected Result
| Product | Latest Recorded Sales |
|---|---|
| Laptop | 75000 |
Explanation
"Laptop"is the product Excel needs to find.B2:B6contains product names.C2:C6contains sales amounts.- The fourth argument is left empty because no custom message is required.
-1as the search_mode tells Excel to search from last to first.- Excel finds the last Laptop record and returns 75000.
Concept Covered
Using search_mode -1 to search from the bottom of a list and retrieve the last matching record.
Student Practice Tasks
Try these tasks yourself using the XLOOKUP function.
Task 1: Find an Employee’s Department
| Employee ID | Name | Department |
|---|---|---|
| E301 | Amit | IT |
| E302 | Rahul | HR |
| E303 | Neha | Sales |
| E304 | Priya | Finance |
Find the department of employee E303.
Task 2: Find a Product’s Price
| Product ID | Product | Price |
|---|---|---|
| P401 | Laptop | 55000 |
| P402 | Mobile | 25000 |
| P403 | Tablet | 18000 |
| P404 | Printer | 12000 |
Find the price of product P402.
Task 3: Find Student Marks
| Roll No. | Name | English | Maths | Science |
|---|---|---|---|---|
| 201 | Ankit | 75 | 80 | 85 |
| 202 | Neha | 88 | 92 | 90 |
| 203 | Rahul | 70 | 78 | 82 |
| 204 | Simran | 95 | 89 | 93 |
Retrieve the Science marks of Roll Number 204.
Task 4: Find an Incentive Percentage
| Minimum Sales | Incentive |
|---|---|
| 0 | 2% |
| 10000 | 3% |
| 20000 | 5% |
| 30000 | 7% |
| 40000 | 10% |
Find the incentive percentage for sales of 25000 using match_mode -1.
Final Student Challenge: Build an Employee Information Finder
Create the following employee table in Excel.
| Employee ID | Name | Department | Salary | City |
|---|---|---|---|---|
| E101 | Rahul | IT | 35000 | Delhi |
| E102 | Priya | HR | 40000 | Noida |
| E103 | Aman | Sales | 45000 | Gurgaon |
| E104 | Neha | Finance | 50000 | Delhi |
| E105 | Karan | IT | 55000 | Noida |
| E106 | Simran | Marketing | 48000 | Gurgaon |
Create a separate search area:
| Search Field | Value |
|---|---|
| Employee ID | E103 |
| Employee Name | |
| Department | |
| Salary | |
| City |
Your Tasks
- Retrieve the employee’s name using Employee ID.
- Retrieve the employee’s department.
- Retrieve the employee’s salary.
- Retrieve the employee’s city.
- Change the Employee ID and check whether all returned details update correctly.
- Enter an Employee ID that does not exist and display a custom message.
Challenge Goal: Build an employee information finder using XLOOKUP and cell references. This exercise will help you understand how lookup functions are used in real office reports.
Key Takeaways
- XLOOKUP searches for a value in a selected lookup range.
- It returns the corresponding value from a separate return range.
lookup_valuespecifies what you want to find.lookup_arrayspecifies where Excel should search.return_arraycontains the information Excel should return.if_not_foundallows you to display a custom message.- The default match mode is exact match.
- XLOOKUP supports exact, approximate, and wildcard matching.
- It can search from first to last or last to first.
- XLOOKUP can return values from columns to the left or right of the lookup range.
Frequently Asked Questions (FAQs)
1. What is the XLOOKUP function in Excel?
XLOOKUP is an Excel function that searches for a value in one range and returns the corresponding value from another range.
2. What is the syntax of XLOOKUP?
The syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
3. What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches in the first column of a selected table and returns information from a column to its right. XLOOKUP uses separate lookup and return ranges, so it can retrieve information from either the left or right.
4. Does XLOOKUP perform an exact match by default?
Yes. XLOOKUP uses exact matching by default, so you do not need to specify FALSE for a normal exact lookup.
5. What is the purpose of if_not_found in XLOOKUP?
The if_not_found argument allows you to display a custom message or value when Excel cannot find the lookup value.
6. Can XLOOKUP search from right to left?
Yes. You can select separate lookup and return ranges in either order. This allows XLOOKUP to retrieve information from columns located to the left of the lookup range.
7. Can XLOOKUP find the last matching value?
Yes. Use search_mode -1 to search from last to first and return the last matching record.
8. Can XLOOKUP perform approximate matching?
Yes. Use match_mode -1 to find an exact match or the next smaller value, and 1 to find an exact match or the next larger value.
9. Can XLOOKUP use wildcard characters?
Yes. Use match_mode 2 to search using wildcard characters such as *, ?, and ~.
10. Is XLOOKUP available in all Excel versions?
No. XLOOKUP is available in Microsoft 365 and newer supported Excel versions, including Excel 2021 and Excel 2024. It is not available in Excel 2016 and Excel 2019 as a built-in function.

