XLOOKUP Function in Excel Tutorial

Excel Tutorial

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:

  1. Excel searches for the required value in the lookup range.
  2. It identifies the position where the value is found.
  3. It checks the corresponding position in the return range.
  4. 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

ArgumentMeaning
lookup_valueThe value you want Excel to search for.
lookup_arrayThe range where Excel should search for the value.
return_arrayThe range containing the result you want to retrieve.
if_not_foundThe message or value Excel should return when no match is found.
match_modeSpecifies whether you want an exact match, approximate match, or wildcard match.
search_modeSpecifies 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 ModeMeaning
0Exact match. This is the default setting.
-1Exact match or the next smaller value.
1Exact match or the next larger value.
2Wildcard match using *, ?, and ~.

6. search_mode

This argument controls the direction or method of searching.

Search ModeMeaning
1Search from first to last. This is the default.
-1Search from last to first.
2Binary search on data sorted in ascending order.
-2Binary 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 IDEmployee NameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

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:

  1. "E103" is the Employee ID Excel needs to find.
  2. A2:A6 contains the Employee IDs. Excel searches this range.
  3. D2:D6 contains the salaries. Excel will return the value from this range.
  4. Excel finds E103 in the third position of the lookup range.
  5. 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 IDEmployee NameDepartmentSalary
E101RahulIT35000
E102PriyaHR40000
E103AmanSales45000
E104NehaFinance50000
E105KaranIT55000

Assume cell F2 contains E103.

Excel Formula

=XLOOKUP(F2,A2:A6,D2:D6)

Expected Result

Employee IDSalary
E10345000

Explanation

  • F2 contains the Employee ID to search for.
  • A2:A6 is the lookup range containing Employee IDs.
  • D2:D6 is 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 IDEmployee NameDepartmentCity
E201ArjunITDelhi
E202MeeraHRNoida
E203DevSalesGurgaon
E204PoojaFinanceDelhi
E205RohanMarketingNoida

Excel Formula

=XLOOKUP("Sales",C2:C6,B2:B6)

Expected Result

DepartmentEmployee Name
SalesDev

Explanation

  • "Sales" is the department Excel needs to find.
  • C2:C6 contains department names.
  • B2:B6 contains 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 IDProduct NameCategoryPrice
P101LaptopElectronics55000
P102KeyboardAccessories1200
P103MouseAccessories600
P104MonitorElectronics12000
P105PrinterElectronics8500

Assume cell F2 contains P110.

Excel Formula

=XLOOKUP(F2,A2:A6,D2:D6,"Product Not Found")

Expected Result

Search Product IDResult
P110Product Not Found

Explanation

  • F2 contains the Product ID to search for.
  • A2:A6 is the lookup range.
  • D2:D6 contains 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 NumberStudent NameMathematicsEnglishScience
101Aarav857890
102Riya928895
103Mohit758280
104Simran899187
105Kabir807684

Assume cell G2 contains 104.

Excel Formula

=XLOOKUP(G2,A2:A6,E2:E6)

Expected Result

Roll NumberScience Marks
10487

Explanation

  • G2 contains the Roll Number.
  • A2:A6 contains student Roll Numbers.
  • E2:E6 contains 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 SalesIncentive
02%
100003%
200005%
300007%
4000010%

Excel Formula

=XLOOKUP(35000,A2:A6,B2:B6,,-1)

Expected Result

Sales AmountIncentive
350007%

Explanation

  • 35000 is the sales amount to search for.
  • A2:A6 contains minimum sales values.
  • B2:B6 contains incentive percentages.
  • The fourth argument is left empty because no custom message is required.
  • -1 tells 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 NamePrice
Laptop Dell55000
Keyboard Logitech1200
Laptop HP48000
Mouse Wireless600
Monitor Samsung12000

Excel Formula

=XLOOKUP("Laptop*",A2:A6,B2:B6,,2)

Expected Result

Matching ProductPrice
Laptop Dell55000

Explanation

  • "Laptop*" is the lookup value. The asterisk means any number of characters can follow Laptop.
  • A2:A6 contains product names.
  • B2:B6 contains product prices.
  • The fourth argument is left empty because no custom message is required.
  • 2 specifies 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:

SymbolMeaning
*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

DateProductSales
01-Jan-2026Laptop50000
05-Jan-2026Mobile30000
10-Jan-2026Laptop60000
15-Jan-2026Tablet25000
20-Jan-2026Laptop75000

Excel Formula

=XLOOKUP("Laptop",B2:B6,C2:C6,-1)

Expected Result

ProductLatest Recorded Sales
Laptop75000

Explanation

  • "Laptop" is the product Excel needs to find.
  • B2:B6 contains product names.
  • C2:C6 contains sales amounts.
  • The fourth argument is left empty because no custom message is required.
  • -1 as 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 IDNameDepartment
E301AmitIT
E302RahulHR
E303NehaSales
E304PriyaFinance

Find the department of employee E303.

Task 2: Find a Product’s Price

Product IDProductPrice
P401Laptop55000
P402Mobile25000
P403Tablet18000
P404Printer12000

Find the price of product P402.

Task 3: Find Student Marks

Roll No.NameEnglishMathsScience
201Ankit758085
202Neha889290
203Rahul707882
204Simran958993

Retrieve the Science marks of Roll Number 204.

Task 4: Find an Incentive Percentage

Minimum SalesIncentive
02%
100003%
200005%
300007%
4000010%

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 IDNameDepartmentSalaryCity
E101RahulIT35000Delhi
E102PriyaHR40000Noida
E103AmanSales45000Gurgaon
E104NehaFinance50000Delhi
E105KaranIT55000Noida
E106SimranMarketing48000Gurgaon

Create a separate search area:

Search FieldValue
Employee IDE103
Employee Name
Department
Salary
City

Your Tasks

  1. Retrieve the employee’s name using Employee ID.
  2. Retrieve the employee’s department.
  3. Retrieve the employee’s salary.
  4. Retrieve the employee’s city.
  5. Change the Employee ID and check whether all returned details update correctly.
  6. 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_value specifies what you want to find.
  • lookup_array specifies where Excel should search.
  • return_array contains the information Excel should return.
  • if_not_found allows 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.