Alternative to VLOOKUP in Excel: How to Use XLOOKUP() with Examples

← Prev

Microsoft Excel offers several functions to search for and retrieve data from tables. One of the most commonly used functions is VLOOKUP(). However, it has some limitations, such as searching only from left to right and requiring a column index number.

To overcome these limitations, Microsoft introduced the XLOOKUP() function.

The XLOOKUP() function is a new alternative to VLOOKUP() that makes searching and retrieving data easier, more flexible, and more efficient.

In this tutorial, you will learn how to use the XLOOKUP function in Excel with simple, practical examples.

What is the XLOOKUP() Function?

The XLOOKUP() function searches for a specific value in a range or array and returns the corresponding value from another range or array.

Unlike VLOOKUP, XLOOKUP can search in both directions and performs an exact match by default.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example 1:

Here's a basic XLOOKUP Formula. Now suppose, you have an employee table with some data in it.

Employee ID Employee Name Department
101 Rahul Sales
102 Priya HR
103 Amit IT
104 Neha Finance
105 Rohit Marketing

You want to find the department of employee ID 103. The formula will be,

=XLOOKUP(103,A2:A6,C2:C6)

XLOOKUP Example in Excel

The parameters:
103 is the lookup value.
A2:A6 is the lookup array.
C2:C6 is the return array.

Example 2:

In the 2nd example, we'll use XLOOKUP with a cell reference. So, instead of entering a fixed lookup value, you can use a cell reference. You do have to hardcode the value.

Suppose cell F2 contains employee ID 104. The formula will be,

=XLOOKUP(F2,A2:A6,B2:B6)

This formula searches for the employee ID entered in F2 and returns the employee name. The advantage is that you can change the employee ID in F2 without modifying the formula.

XLOOKUP with Cell Reference in Excel

Example 3: XLOOKUP with a Custom Not Found Message

One useful feature of XLOOKUP is that it allows you to display a custom message when a lookup value does not exist.

=XLOOKUP(106,A2:A6,B2:B6,"Employee Not Found")

This formula is useful when working with large datasets because it provides a meaningful message instead of an error.

Example 4:

You can use XLOOKUP to Search values from Right to Left.

One major limitation of VLOOKUP is that it cannot easily return values from columns located to the left of the lookup column. Suppose you table looks like this.

Employee Name Employee ID Salary
Alpha 101 25000
Bravo 102 30000
Charlie 103 35000
Delta 104 40000

You want to find the employee name using employee ID 103.

=XLOOKUP(103,B2:B5,A2:A5)

The result will be: Charlie

Unlike VLOOKUP, XLOOKUP can search in one column and return a result from a column on its left.

Example 5:

Interestingly, you can use XLOOKUP to find approximate matches.

For example, you have "commission" table and you want to calculate the commission rate of ₹25,000. The formual will be,

=XLOOKUP(25000,A2:A5,B2:B5,, -1)

XLOOKUP formula to find Approximate Matches in Excel

The match mode "-1" finds an exact match or the next smaller value. 👉 One This example assumes the sales amount column is sorted in ascending order.

Conclusion

The XLOOKUP() function is a powerful alternative to VLOOKUP() in Microsoft Excel. It offers greater flexibility, simpler formulas, and additional search capabilities.

If you regularly work with employee records, sales reports, product lists, or financial data, learning XLOOKUP can make your Excel work faster and easier.

← Previous