Help Find Closest match in Long Text Strings

[maximillianrg]

Hello Excel Masters

Thank you in advance for your help. I'm looking for a formula that can compare a text string like the one in cell A5 below to thousands of text strings and return the closest match. In the below, the formula would compare cell A5 against cells A1-A3 and because cell A2 is the closest match it would return the value in cell B2 (Pear). I have tried variations of vlookup and in most cases it picks up the first or last but not the best match.

	A	B
1	The Quick Brown	Apple
2	The Quick Brown Fox Jumps	Pear
3	The Quick Brown Fox	Banana
4
5	The quick brown fox jumps over the lazy dog	?????
6

 

[KRice]

Is the version of Excel in your profile up to date?...Excel 2016? I ask because Microsoft created an add-in for fuzzy matching, but it is designed for Excel 365:

Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

The Fuzzy Lookup Add-In for Excel performs fuzzy matching of textual data in Excel.

www.microsoft.com

I needed to dial the "similarity threshold" down to about 0.25 before the algorithm would choose "Pear" for the 1st phrase (it returned a blank when a higher threshold was set). To use it, one set of information is used as a lookup table...and a formal Excel table is created from it. The other data for which you want a match is also converted to a table. The fuzzy match add-in then allows you to specify the relationships between various columns of the two tables and to adjust the similarity threshold. Results are returned in a separate table:

MrExcel_20220603.xlsx
	A	B	C	D	E
1	Phrase	Return		TextShorts	Return
2	The quick brown fox jumps over the lazy dog			The Quick Brown	Apple
3	Brown fox jumps			The Quick Brown Fox Jumps	Pear
4	The brown box			The Quick Brown Fox	Banana
5
6
7	Phrase	Return	Similarity
8	The quick brown fox jumps over the lazy dog	Pear	0.8476
9	Brown fox jumps	Pear	0.9200
10	The brown box	Banana	0.8020
Sheet13

If you don't have 365, you might investigate using Power Query, as it too has a fuzzy match capability and it is part of Excel 2016.

 

[bebo021999]

Book1
	A	B
1	Phrase	Return
2	The Quick Brown	Apple
3	The Quick Brown Fox Jumps	Pear
4	The Quick Brown Fox	Banana
5
6
7	Phrase	Return
8	The quick brown fox jumps over the lazy dog	Pear
Sheet1

Cell Formulas
Range	Formula
B8	B8	=LOOKUP(2,1/(LEN(SUBSTITUTE(LOWER(A8),LOWER($A$2:$A$4),""))=AGGREGATE(15,6,LEN(SUBSTITUTE(LOWER(A8),LOWER($A$2:$A$4),"")),1)),$B$2:$B$4)