Sun. Oct 12th, 2025

XLOOKUP is a function in Excel that was introduced in 2019, and it’s quickly become one of the most powerful and flexible lookup functions available. In this tutorial, we’ll cover everything you need to know to start using XLOOKUP in your own Excel workbooks.

What is XLOOKUP?

XLOOKUP is a function in Excel that allows you to look up a value in a table or range and return a corresponding value from another column in the same table or range. It’s similar to other lookup functions like VLOOKUP and HLOOKUP, but it offers several advantages over these functions, including:

  • It can look up values to the left or right of the lookup column
  • It can return an array of results (up to the number specified in the “return array” argument)
  • It can perform exact or approximate matches
  • It can handle errors and empty cells more gracefully

Overall, XLOOKUP is a more versatile and user-friendly lookup function than its predecessors.

How to use XLOOKUP

To use XLOOKUP, you need to provide four pieces of information:

  1. The lookup value: This is the value you want to look up in the table or range. It can be a cell reference or a literal value.
  2. The lookup array: This is the range or array of values you want to search for the lookup value. It must be a single column or row of values.
  3. The return array: This is the range or array of values you want to return if the lookup value is found in the lookup array. It can be a single column or row of values, or it can be a multi-column or multi-row range.
  4. The match mode: This is a value that determines whether XLOOKUP should perform an exact or approximate match. It can be either “0” (exact match) or “1” (approximate match).

Once you’ve gathered these four pieces of information, you can use the following formula to perform a lookup with XLOOKUP:

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

Note that the “match_mode” and “search_mode” arguments are optional. If you omit them, XLOOKUP will perform an exact match and a search from top to bottom.

Examples

Let’s look at some examples to see how XLOOKUP works in practice.

Example 1: Exact Match

Suppose you have a table of employee data, and you want to look up the salary for a specific employee by their ID number. Here’s what the table looks like:

IDNameDepartmentSalary
101JohnSales50000
102MaryMarketing60000
103BobIT55000
104JaneHR45000

To perform an exact match using XLOOKUP, you could use the following formula:

=XLOOKUP(101, A2:A5, D2:D5)

This formula looks up the value “101” in the range A2:A5 (the ID column), and returns the corresponding value from the range D2:D5 (the Salary column). In this case, the formula returns “50000”.

Example 2: Approximate Match

Suppose you have a table of sales data, and you want to look up the commission rate for a specific sales amount. Here’s what the table looks like:

Sales AmountCommission Rate
100005%
250007%
5000010%
10000015%

To perform an approximate match using XLOOKUP, you could use the following formula:

=XLOOKUP(60000, A2:A5, B2:B5, 1, 1)

This formula looks up the value “60000” in the range A2:A5 (the Sales Amount column), and returns the corresponding value from the range B2:B5 (the Commission Rate column). Because we specified a match mode of “1” (approximate match), XLOOKUP will return the commission rate for the nearest sales amount that is less than or equal to 60000, which is 7%.

Example 3: Return Array

Suppose you have a table of customer data, and you want to look up the name, email address, and phone number for a specific customer ID. Here’s what the table looks like:

IDNameEmailPhone
101Johnjohn@example.com555-1234
102Marymary@example.com555-5678
103Bobbob@example.com555-9101
104Janejane@example.com555-2345

To return multiple values using XLOOKUP, you can use an array formula. Here’s the formula you could use to look up the data for customer ID 101:

=XLOOKUP(101, A2:A5, {B2:B5, C2:C5, D2:D5})

Note that this is an array formula, so you need to enter it with CTRL+SHIFT+ENTER instead of just ENTER. This formula looks up the value “101” in the range A2:A5 (the ID column), and returns the corresponding values from the ranges B2:B5, C2:C5, and D2:D5 (the Name, Email, and Phone columns). In this case, the formula returns the array {“John”, “john@example.com“, “555-1234”}.

Conclusion

XLOOKUP is a powerful and flexible lookup function in Excel that offers many advantages over other lookup functions like VLOOKUP and HLOOKUP. By using XLOOKUP, you can perform exact or approximate matches, look up values to the left or right of the lookup column, return arrays of results, and handle errors and empty cells more gracefully. With this tutorial, you should have everything you need to start using XLOOKUP in your own Excel workbooks.

Leave a Reply

Your email address will not be published. Required fields are marked *