Quick XLOOKUP Guide

The XLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a range of cells and return a corresponding value from another range of cells. It’s an upgraded version of the VLOOKUP and HLOOKUP functions with more advanced features.

The syntax for the XLOOKUP function is as follows:

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

The arguments are defined as follows:

• lookup_value: The value you want to search for.
• lookup_array: The range of cells where you want to search for the lookup_value.
• return_array: The range of cells from which you want to retrieve a value.
• if_not_found (optional): The value to return if no match is found.
• match_mode (optional): The match mode to use. This can be set to 0 (exact match), 1 (exact match or next smallest value), -1 (exact match or next largest value), or 2 (wildcard match).
• search_mode (optional): The search mode to use. This can be set to 1 (search from the beginning), -1 (search from the end), or 2 (search in any order).

Example 1: Basic XLOOKUP function Suppose you have a table of product names and their corresponding prices, and you want to find the price of a specific product. You can use the XLOOKUP function to do this.

1. First, select the cell where you want to display the result.
2. Type the XLOOKUP function with the following arguments: =XLOOKUP("Product Name",A2:A6,B2:B6)
3. Replace “Product Name” with the name of the product you want to search for.
4. Replace A2:A6 with the range of cells that contain the product names.
5. Replace B2:B6 with the range of cells that contain the corresponding prices.

Example 2: Using the if_not_found argument If the lookup_value is not found in the lookup_array, you can use the if_not_found argument to return a specific value. For example:

1. Suppose you want to return “Not Found” if the product name is not found in the table.
2. Type the XLOOKUP function with the following arguments: =XLOOKUP("Product Name",A2:A6,B2:B6,"Not Found")

Example 3: Using the match_mode argument The match_mode argument allows you to control the type of match that is used when searching for the lookup_value. For example:

1. Suppose you want to return the price of the next smallest product if the exact match is not found.
2. Type the XLOOKUP function with the following arguments: =XLOOKUP("Product Name",A2:A6,B2:B6,"Not Found",1)

Example 4: Using the search_mode argument The search_mode argument allows you to control the direction of the search when looking for the lookup_value. For example:

1. Suppose you want to search for the product name from the end of the lookup_array.
2. Type the XLOOKUP function with the following arguments: =XLOOKUP("Product Name",A2:A6,B2:B6,"Not Found",0,-1)

In conclusion, the XLOOKUP function is a powerful tool that allows you to search for a specific value in a range of cells and return a corresponding value from another range of cells. With its advanced features, it can help you to quickly and easily retrieve data from large tables in Excel.