Example 1:

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

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

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

Example 2:

Another handy feature of XLOOKUP is that it can search based on multiple criteria (using the '&' ampersand character for this). For example, if we have a list of customers and their orders and we want to find the date on which 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 look up (customer name and product), D2:D20 and C2:C20 are the ranges we want to search in (customer name and product), and C2:C20 is the range where we want to find the date. This will retrieve the date the customer ordered the specific product.

Tip:
Note that in the fourth field of the XLOOKUP, we can specify a text to display when the value being searched for cannot be found. It currently shows "wrong product or name", whereas with VLOOKUP you'd otherwise get an error message (NA). With the old VLOOKUP, we then had to nest the extra IFERROR function. With XLOOKUP, that's no longer necessary.

XLOOKUP is therefore a very useful new function that makes it easier to search for and find data in Excel. With its versatility, it's definitely worth learning how to use it.