The XLOOKUP function is a powerful lookup function in Microsoft Excel that allows you to search for a value in a range or an array and return a corresponding value from another range or array. It offers more flexibility and functionality compared to traditional lookup functions like VLOOKUP or HLOOKUP. Here’s how you can use the XLOOKUP function:
Syntax
The basic syntax of the XLOOKUP function is as follows:
1.-lookup_value(required): The value you want to find in the lookup_array.
2.-lookup_array(required): The range or array where you want to search for the lookup_value.
3.-return_array(required): The range or array from which you want to return a corresponding value.
4.– if_not_found (optional): This parameter specifies the value or action to take if no match is found. You can use a specific value, such as a text string or a number, or use an error handling action, such as “#N/A”, “#REF!”, etc.
5.-match_mode (optional): This determines how the lookup value should be matched within the lookup table. It can be one of the following values:
1 or “exact”: Matches the lookup value exactly.
0 or “wildcard”: Allows wildcard characters (*) in the lookup value to match multiple values.
-1 or “wildcard_and_exact”: Allows both exact matches and wildcard matches.
6.-search_mode (optional): This determines the direction in which the lookup table should be searched. It can be one of the following values:
1 or “next”: Searches from top to bottom.
-1 or “previous”: Searches from bottom to top.
It’s important to note that the [if_not_found], [match_mode], and [search_mode] parameters are optional, and if you don’t specify them, Excel will use the default settings.
Examples
Example 1
Here are a few examples to illustrate the usage of the XLOOKUP function:
Let’s assume you have a table with five columns: A, B, C, D, and E. The lookup_value is in column C, and you want to find the corresponding value in column A based on that lookup_value.
Here’s the structure of your table:
To perform the lookup, you can use the XLOOKUP function in the following way:
=XLOOKUP(lookup_value, D2:D5, A2:A5)
In this formula:
lookup_value is the value you want to search for, located in column D. For example, if you want to find “lookup_value”, you can replace lookup_value with D2.
D2:D5 is the range that contains the lookup values.
A2:A5 is the range that contains the corresponding values you want to retrieve from column A.
By entering this formula, the XLOOKUP function will search for the specified lookup_value in column D and return the corresponding value from column A.
For example, if you use the formula =XLOOKUP(D2, D2:D5, A2:A5), it will search for “Blue” in column D and return the corresponding value “Product A” from column A.
Remember to adjust the ranges and cell references in the formula according to the specific range and location of your own data.
Example 2
Suppose we want to search for the details of “Product E”. However, “Product E” does not exist in the table. In this case, the XLOOKUP function will return “Not Found” for all the columns.
Let’s break down the formula:
=XLOOKUP("Product E", A2:A5, D2:D5, "Not Found")
“Product E” is the lookup_value, which is the product we are searching for.
A2:A5 is the lookup_array, the range of cells that contains the product names.
D2:D5 is the return_array, the range of cells that contains the color for the products.
“Not Found” is the if_not_found parameter, specifying the value to display if the product name is not found.
Since “Product E” is not present in the lookup_array (A2:A5), the XLOOKUP function will return “Not Found” as the result for all the columns.
The result of the formula would be:
In this case, “Not Found” is displayed as the result for Color columns since there is no corresponding data available for “Product E”
The XLOOKUP function is a powerful lookup function in Microsoft Excel that allows you to search for a value in a range or an array and return a corresponding value from another range or array. It offers more flexibility and functionality compared to traditional lookup functions like VLOOKUP or HLOOKUP. Here’s how you can use the XLOOKUP function:
