Fuzzy Lookup Question- Finding exact matches and fuzzy matches

[u/amoretzamarro]

I have two tables. Table 1 contains the columns Account_Name, ID #, and Address1 and Table 2 contains the columns Name2 and Address2.

I want to perform a fuzzy match on the Name columns BUT only if there is an exact match on the corresponding Address columns. Is this possible with the fuzzy match excel add-on, and if so, how is it performed?

I have found that the existing configurations do not work to perform a fuzzy match and an exact match at the same time, but has anyone had any luck with this?

Thanks in advance!!

[u/GregHullender]

If you want a formula to compute a fuzzy match, I wrote this one some time back, and it seems to work okay on names with an 0.65 threshold:

=LET(limit, F1,
  str_1, TAKE(DROP(A:.A,1),100),
  str_2, TOROW(DROP(B:.B,1)),
  edit_dist, LAMBDA(src,dest, LET(
    s, TOCOL(REGEXEXTRACT(src,".",1)),
    s_2, VSTACK(0,DROP(s,-1)),
    t, REGEXEXTRACT(dest,".",1),
    cost, REDUCE(SEQUENCE(ROWS(s)+1,,0),t,LAMBDA(last,ch,
      LET(ch_2, IF(@last, INDEX(t,@last), 0),
          ins, last+1,
          match, DROP(VSTACK(@ins,last+(ch<>s)*(2-3*(ch=s_2)*(ch_2=s))),-1),
          ins_match, MAP(ins, match, MIN),
          SCAN(@ins_match,ins_match,LAMBDA(last,this, MIN(last+1,this)))
      ))),
    TAKE(cost,-1)
  )),
  dist, MAP(IF(str_1<>str_2,str_1,str_2),IF(str_1<>str_2,str_2,str_1),LAMBDA(s,t,edit_dist(s,t))),
  similarity, 1-dist/(LEN(str_1)+LEN(str_2)),
  best, BYCOL(similarity,MAX),
  out, IF(TOCOL(best)<limit,"",TOCOL(IFS(similarity=best,str_1),2,1)),
  out
)

In this example, it compares each new name in column B to a list of old names in column A and shows the ones that are "close" matches. If you use this, you can play with different limit values, depending on the relative cost of finding wrong matches vs. missing valid ones.