How to perform an fuzzy search (forward, partial, or backward match) with the VLOOKUP function.(Microsoft Excel)
The normal use of the VLOOKUP function is to perform two types of searches.
- Search type False: Get exact matches.
- Search type True: Get the closest match.
However, when wildcards are combined, it is possible to perform ambiguous searches on strings using forward, partial, and backward matching.
Contents
Steps
Use the wildcard * (asterisk) as the first argument (search value) of the VLOOKUP function.
If you want to treat * as a character asterisk instead of a wildcard, you must use ~ (tilde) preceded by ~*.
| Match type | Specification Method. |
|---|---|
| Forward match | Concatenate "*" after the search value. |
| Partial match | Concatenate "*" before and after the search value. |
| Backward match | Concatenate "*" before the search value. |
For XLOOKUP function
Wildcards are available in the XLOOKUP function as well.
However, the wildcard will not work unless 2 is specified for the Match mode(Fifth parameter).
---