Find the Closest Match in Excel

Question

Looking for a formula in excel for where I have two lists containing business names and addresses with incomplete data, so I wish to lookup a column of names and find an exact match, but if an exact match is not found then look for the closest match based on most common words and numbers.

For example in List A, a cell contains "BAIRNSDALE 17 BAILEY ST BAIRNSDALE", and List B contains "BAIRNSDALE BAILEY ST BAIRNSDALE", although not an exact match it has the most amount of common words and numbers from the entire list so this would be a match, so the corresponding List B ID would be shown in a new column

Once found I can then apply a % match

I have combined Business Name, Address and Suburb in a single cell, for the two sets of lists, sorted and then a vlookup and omitting range lookup

Answer 1

If you don't want to use fuzzy lookup as it was pointed out in the comment, you can emulate it, as follows as you described in the question:

=LET(x,TEXTSPLIT(A1,," "), y,TEXTSPLIT(B1,," "), max,ROWS(y),
 SUM(1-ISNA(XMATCH(x,y)))/max)