What XLOOKUP can do (much) better than VLOOKUP

What is the XLOOKUP function?

XLOOKUP is a powerful function that allows you to search and retrieve data in a table. Unlike VLOOKUP and HLOOKUP, XLOOKUP can find data that isn’t in the first column (or row) of a table (Example 1). It can also retrieve data based on multiple criteria (Example 2).

Example 1:

  • Suppose we have a list of products and their prices (A1 tem D18), and we want to find the prices (G2 tem G5) of the products (F2 tem F5). This example can be easily solved with XLOOKUP, unlike VLOOKUP.

= XLOOKUP (F2; D1: D18; B1: B18)

In this formula, Product Name (F2) is the value we want to search, D2: D18 is the range in which we want to search, and B2: B18 is the range in which we want to find the price. This will retrieve the price of the product based on the product name.

Example 2:

Another useful feature of XLOOKUP is that it can search based on multiple criteria (use the ‘&‘ or ampersand sign for this). For example, if we have a list of customers and orders and we want to find on which date a specific customer ordered a specific product, we can use the following formula:

= XLOOKUP ($G2&$H2; $D$2:$D$20&$B$2:$B$20; $C$2:$C$20; “wrong date or name”;0)

In this formula, G2 and H2 are the values we want to search (customer name and product), D2:D20 and C2:C20 are the ranges in which we want to search (customer name and product), and C2:C20 is the range in which we want to find the date. This will retrieve the date of the customer’s order for the specific product.

Note that in the fourth field of the XLOOKUP, we can specify a text when the searched value can’t be found. It now reads “wrong product or name” whereas otherwise there would be an error message via VLOOKUP (NA). In the old VLOOKUP, we had to nest the additional function IFERROR. So through XLOOKUP, this is simply no longer necessary.

XLOOKUP is a very useful new feature that makes it easier to search and find data in Excel. Because of its versatility, it is definitely worth learning how to use it.

Share this customer case

Let's talk about your next project.

Team Xylos is ready to meet you!

Other interesting blogs

Learning Solutions

AG Insurance finetunes digital skills thanks to Xylos trainers

Cloud & Data

Cloud-Hopping: The Untold Ease of Switching Your CSP


Farys fixes leaks in disaster recovery plan


Neuhaus renews hybrid data center

Learning Solutions

Level: Excel Expert

Learning Solutions

Belfius takes communication and collaboration to the cloud