Share this article:

XLOOKUP is a powerful function in Excel that can be used to look up and retrieve data from a table. In this article, we will discuss 10 ways that XLOOKUP can be used in Excel, along with the data used and the result.

Example 1: Looking up a Value in a Table

Suppose you have a list of products and their prices. You want to look up the price of a particular product.

ProductPrice
A10
B20
C30
D40

To look up the price of product B, you can use the following formula:

=XLOOKUP(“B”,A2:A5,B2:B5)

This formula looks up the value “B” in the range A2:A5 and returns the corresponding value from the range B2:B5. The result will be as follows:

ProductPrice
A10
B20
C30
D40

Example 2: Looking up a Value and Returning a Different Value

Suppose you have a list of products and their prices. You want to look up the price of a particular product, but you want to return a message if the product is not found.

ProductPrice
A10
B20
C30
D40

To look up the price of product E and return a message if the product is not found, you can use the following formula:

=XLOOKUP(“E”,A2:A5,B2:B5,”Product not found”)

This formula looks up the value “E” in the range A2:A5 and returns the corresponding value from the range B2:B5. If the value is not found, the formula returns the message “Product not found.” The result will be as follows:

Result
Product not found

Example 3: Looking up Values in Reverse Order

Suppose you have a list of products and their prices, but the products are listed in reverse order. You want to look up the price of product B.

ProductPrice
D40
C30
B20
A10

To look up the price of product B, you can use the following formula:

=XLOOKUP(“B”,A2:A5,B2:B5)

This formula looks up the value “B” in the range A2:A5 and returns the corresponding value from the range B2:B5. The result will be as follows:

ProductPrice
D40
C30
B20
A10

Example 4: Returning Multiple Values

Suppose you have a list of products and their prices, and you want to return the prices for multiple products at once.

ProductPrice
A10
B20
C30
D40

To return the prices for products A and B, you can use the following formula:

=XLOOKUP({“A”,”B”},A2:A5,B2:B5)

This formula looks up the values “A” and “B” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:

Result
10
20

Example 5: Returning the Nearest Value

Suppose you have a list of grades and you want to find the nearest grade to 70.

Grade
60
65
70
75
80

To find the nearest grade to 70, you can use the following formula:

=XLOOKUP(70,A2:A6,B2:B6,,1)

This formula looks up the value 70 in the range A2:A6 and returns the corresponding value from the range B2:B6. If the value is not found, the formula returns the nearest value. The result will be as follows:

Result
70

Example 6: Looking up Values in a Non-Contiguous Range

Suppose you have a list of products and their prices, and you want to look up the prices for a non-contiguous range of products.

ProductPrice
A10
B20
C30
D40

To look up the prices for products A and C, you can use the following formula:

=XLOOKUP({“A”,”C”},A2:A5,B2:B5)

This formula looks up the values “A” and “C” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:

Result
10
30

Example 7: Looking up Values in a Vertical Range

Suppose you have a list of products and their prices, and you want to look up the prices in a vertical range.

ProductPrice 1Price 2
A1020
B3040
C5060

To look up the price of product B in the vertical range, you can use the following formula:

=XLOOKUP(“B”,A2:A4,B2:C4)

This formula looks up the value “B” in the range A2:A4 and returns the corresponding value from the range B2:C4. The result will be as follows:

ProductPrice 1Price 2
B3040

Example 8: Handling Errors

Suppose you have a list of products and their prices, and you want to handle errors when a value is not found.

ProductPrice
A10
B20
C30
D40

To look up the price of product E and handle the error, you can use the following formula:

=XLOOKUP(“E”,A2:A5,B2:B5,,,”Not found”)

This formula looks up the value “E” in the range A2:A5 and returns the corresponding value from the range B2:B5. If the value is not found, the formula returns the message “Not found.” The result will be as follows:

Result
Not found

Example 9: Looking up Values with Wildcards

Suppose you have a list of products and you want to look up the prices for products that have a name that contains the word “apple.”

ProductPrice
Red Apple10
Green Apple20
Orange30
Grape40

To look up the prices for products that contain the word “apple,” you can use the following formula:

=XLOOKUP(“apple“,A2:A5,B2:B5)

This formula looks up the value “apple” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:

Result
10
20

Example 10: Looking up Values with Multiple Criteria

Suppose you have a list of orders that includes the order number, the date the order was placed, and the salesperson who made the sale. You want to look up the order number for an order that was placed by John after March 1, 2023.

Order NumberOrder DateSalesperson
10013/2/2023John
10023/3/2023Sarah
10033/4/2023John
10043/5/2023Tom
10053/6/2023John

To look up the order number for an order that was placed by John after March 1, 2023, you can use the following formula:

=XLOOKUP(1,(C2:C6=”John”)*(B2:B6>DATE(2023,3,1)),A2:A6)

This formula looks up the values 1 in the array (C2:C6=”John”)*(B2:B6>DATE(2023,3,1)) and returns the corresponding values from the range A2:A6. The result will be as follows:

Result
1003

Conclusion

XLOOKUP is a powerful function in Excel that can be used to look up and retrieve data from a table. By using XLOOKUP, you can perform a wide range of tasks in Excel, from looking up a single value to looking up values with multiple criteria. With these examples, you can get started with using XLOOKUP in your own Excel spreadsheets.