Excel is a widely-used spreadsheet software that provides users with a variety of functions to manipulate and analyze data. One of the more recent additions to the suite of functions is XLOOKUP, which allows users to search for data based on a specified value and retrieve data from a corresponding column. XLOOKUP is a powerful function that can be used in many different scenarios, and in this tutorial, we will explore how to use XLOOKUP for partial matches.
XLOOKUP:
XLOOKUP is a newer function in Excel that was introduced in the 2019 version. It has quickly become one of the most useful and versatile functions in the software, allowing users to perform complex lookups and retrievals with ease. The XLOOKUP function has four arguments that must be specified in order to work properly:
- Lookup_value: This is the value that you want to search for in the table. It can be a single value or a reference to a cell.
- Lookup_array: This is the range of cells that contains the values that you want to search for. It must be a single column or row.
- Return_array: This is the range of cells that contains the data that you want to retrieve. It must be the same size as the Lookup_array and can be multiple columns or rows.
- Match_mode: This determines how Excel should match the Lookup_value to the values in the Lookup_array. There are four options: 0 (exact match), -1 (exact match or next smallest), 1 (exact match or next largest), and 2 (wildcard match).
Using XLOOKUP for partial matches:
One of the most powerful features of XLOOKUP is its ability to perform partial matches. This means that you can search for a value that only partially matches the data in the Lookup_array and still retrieve the corresponding data from the Return_array. To do this, you will need to use the wildcard match option (Match_mode=2).
Let’s consider an example. Suppose we have a table of data that contains a list of product names and their prices:
| Product Name | Price |
|---|---|
| Apples | $1.00 |
| Bananas | $0.50 |
| Oranges | $0.75 |
| Grapes | $2.00 |
| Pineapple | $3.00 |
Now suppose we want to retrieve the price of a product based on a partial match of its name. For example, if we search for “an”, we want to retrieve the prices of both Apples and Bananas. Here’s how we can do it:
- Start by selecting the cell where you want to display the result. In this case, let’s say we want to display the result in cell C2.
- Enter the XLOOKUP function:
=XLOOKUP(""&B2&"",A2:A6,B2:B6,0,2)
In this formula, we’re using the wildcard match option (Match_mode=2) by specifying the fourth argument as “2”. We’re also using the “&” operator to concatenate the asterisk wildcard character before and after the Lookup_value. This allows us to perform a partial match.
- Press Enter to calculate the formula. The result should be $1.00, which is the price of Apples.
- To retrieve the price of Bananas as well, simply copy the formula down to cell C3. The result should be $0.50.
Explanation of the formula:
Here’s a breakdown of what’s happening in the XLOOKUP formula:
““&B2&”“: This concatenates the Lookup_value (B2) with an asterisk wildcard character before and after it. So if B2 contains the value “an”, the resulting Lookup_value will be “an“.
A2:A6: This is the range of cells that contains the product names we want to search for.
B2:B6: This is the range of cells that contains the prices we want to retrieve.
0: This specifies that we want an exact match. Since we’re using the wildcard match option, this argument is technically not necessary, but it’s good practice to specify it anyway.
2: This specifies that we want to use the wildcard match option.
When we enter this formula and press Enter, Excel searches for any values in the A2:A6 range that contain the string “an”. Since both Apples and Bananas contain this string, Excel returns the prices of both products, which we can see in cells C2 and C3.
Conclusion:
XLOOKUP is a powerful and versatile function in Excel that allows users to perform complex lookups and retrievals with ease. By using the wildcard match option (Match_mode=2), we can perform partial matches and retrieve data from corresponding columns in a table. This can be especially useful when working with large datasets or when searching for values that are not exact matches. With a little practice, anyone can become proficient at using XLOOKUP for partial matches and other advanced lookups in Excel.