This video demonstrates how to perform fuzzy or partial match lookups in Excel.  There are two scenarios where you might need to use a partial match:

  1. When the lookup value is partial
  2. When the lookup table contains partial strings.

The video covers both scenarios.

In Office 365 we can use XLOOKUP and FILTER to perform fuzzy or partial matches, but in older versions of Excel we have to use VLOOKUP, MATCH and INDEX. The SEARCH and ISNUMBER functions are also used. The video demonstrates how to use all these functions, so no matter what version of Excel you are using you will find a solution here.

Download the feature file: Old Excel Version, Office 365 Version

Formulas featured in the video where the lookup table contains the partial string

XLOOKUP

=B6*(1-XLOOKUP(TRUE,ISNUMBER(SEARCH($F$6:$F$11,A6)),$G$6:$G$11,0))

FILTER

=B6*(1-FILTER($G$6:$G$11,ISNUMBER(SEARCH($F$6:$F$11,A6)),0))

INDEX & MATCH

{=B6*(1-IFERROR(INDEX($F$6:$F$11,MATCH(TRUE,ISNUMBER(SEARCH($E$6:$E$11,A6)),0)),0))}

Formulas featured in the video where the lookup value is the partial string

XLOOKUP

=XLOOKUP("*"&J6&"*",$M$6:$M$25,$N$6:$N$25,,2)

VLOOKUP

=VLOOKUP("*"&I6&"*",$L$6:$M$25,2,0)

Posted by Blue Pecan Computer Training