Excel XLOOKUP Wildcards: Functionality Change and Usage
In this article, we will discuss the usage of XLOOKUP wildcards, focusing on the Match\_Mode 2 functionality. XLOOKUP is a powerful and flexible function in Excel that allows users to search for data in a range of cells and return a corresponding value. With the help of wildcards, XLOOKUP becomes even more versatile, enabling users to find and match partial text strings.
Understanding XLOOKUP Wildcards
XLOOKUP wildcards are special characters that can be used to represent one or more characters in a text string. Excel supports three wildcard characters:
- ?: Represents any single character
- \*: Represents any sequence of characters, including an empty sequence
- ~: Escapes the next character, allowing you to search for special characters, such as wildcards themselves
Match\_Mode 2: Wildcards Match
Match\_Mode 2 in XLOOKUP allows users to perform a wildcard match, where the lookup\_value can contain wildcard characters. This functionality is particularly useful when searching for partial matches or when the exact text string is unknown.
Usage Example
Consider the following dataset:
| A | B |
|---|---|
| 1 | Product |
| 2 | Apple iPhone 12 |
| 3 | Samsung Galaxy S21 |
| 4 | Google Pixel 6 |
| 5 | OnePlus 9 Pro |
To find all products containing the word "iPhone" using Match\_Mode 2, you can use the following formula:
=XLOOKUP("*iPhone*", A2:A5, B2:B5, "Not found", 2)
This formula will return the value "Apple iPhone 12" from cell B2.
Additional Resources
For more information on XLOOKUP and wildcards, consider the following resources:
This article has provided an overview of XLOOKUP wildcards, focusing on the Match\_Mode 2 functionality. By utilizing wildcards in XLOOKUP, users can perform powerful and flexible searches for partial text strings in their datasets.