5 Ways to Use XLOOKUP

Introduction to XLOOKUP

The XLOOKUP function is a powerful and flexible lookup function in Excel that allows users to search for a value in a table and return a corresponding value from another column. It was introduced in Excel 2019 and has since become a popular alternative to the VLOOKUP and INDEX/MATCH functions. In this article, we will explore five ways to use XLOOKUP to solve common lookup problems.

1. Basic Lookup

The most basic use of XLOOKUP is to search for a value in a table and return a corresponding value from another column. The syntax for XLOOKUP is:

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

For example, suppose we have a table with employee names and salaries, and we want to find the salary of a specific employee.

Name	Salary
John	50000
Jane	60000
Bob	70000

We can use XLOOKUP to find the salary of John like this:

=XLOOKUP(“John”, A2:A4, B2:B4)

This formula searches for the value “John” in the range A2:A4 and returns the corresponding value in the range B2:B4.

📝 Note: The lookup_array and return_array ranges must be the same size and orientation.

2. Approximate Match

XLOOKUP also allows us to perform approximate matches by using the [match_mode] argument. For example, suppose we have a table with product codes and prices, and we want to find the price of a product with a code that is close to, but not exactly, a specific value.

Product Code	Price
ABC123	10.99
ABC124	11.99
ABC125	12.99

We can use XLOOKUP with an approximate match like this:

=XLOOKUP(“ABC12*”, A2:A4, B2:B4, , 2)

This formula searches for a value in the range A2:A4 that starts with “ABC12” and returns the corresponding value in the range B2:B4.

3. Multiple Criteria Lookup

XLOOKUP can also be used to perform lookups with multiple criteria. For example, suppose we have a table with customer names, addresses, and phone numbers, and we want to find the phone number of a customer with a specific name and address.

Name	Address	Phone Number
John	123 Main St	555-1234
Jane	456 Elm St	555-5678
Bob	789 Oak St	555-9012

We can use XLOOKUP with multiple criteria like this:

=XLOOKUP(1, (A2:A4=“John”) * (B2:B4=“123 Main St”), C2:C4)

This formula searches for a row in the table where the name is “John” and the address is “123 Main St”, and returns the corresponding phone number.

4. Vertical and Horizontal Lookup

XLOOKUP can be used to perform both vertical and horizontal lookups. For example, suppose we have a table with employee names in the first column and department names in the first row, and we want to find the department of a specific employee.

	Sales	Marketing	IT
John	X
Jane		X
Bob			X

We can use XLOOKUP to find the department of John like this:

=XLOOKUP(“John”, A2:A4, B1:D1)

This formula searches for the value “John” in the range A2:A4 and returns the corresponding value in the range B1:D1.

5. Handling Errors

XLOOKUP also allows us to handle errors that may occur during the lookup process. For example, suppose we have a table with customer names and addresses, and we want to find the address of a customer with a specific name, but the customer does not exist in the table.

Name	Address
John	123 Main St
Jane	456 Elm St
Bob	789 Oak St

We can use XLOOKUP with error handling like this:

=XLOOKUP(“Alice”, A2:A4, B2:B4, “Customer not found”)

This formula searches for the value “Alice” in the range A2:A4 and returns the corresponding value in the range B2:B4. If the value is not found, it returns the string “Customer not found”.

In summary, XLOOKUP is a powerful and flexible lookup function in Excel that allows users to search for a value in a table and return a corresponding value from another column. It can be used to perform basic lookups, approximate matches, multiple criteria lookups, vertical and horizontal lookups, and error handling.