How to perform an fuzzy search (forward, partial, or backward match) with the VLOOKUP function.(Microsoft Excel)

Japanese version.

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.


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 typeSpecification Method.
Forward matchConcatenate "*" after the search value.
Partial matchConcatenate "*" before and after the search value.
Backward matchConcatenate "*" 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).



Tips. XLOOKUP & VLOOKUP & HLOOKUP(Microsoft Excel)