Creating a Fuzzy Text Lookup Match using Match Index

[bearcub]

I have am using he following formula to put a value based upon a company name. Sometimes I get an error message because the criteria doesn't match the name on the destination worksheet.

The issue might be a period is in 1 name but not the other (Corp. instead of Corp) or corporation might be spelled for one customer name but not the other. I am trying to extract text from a different report not a value.


This is my formula. Should I replace that formula with one of the newer array formulas or is there another workaround:

"INDEX(DelphixDataExtractSalesOrdersP!C:C, MATCH("*" & 'Waterfall & Recon'!A8 &"*", DelphixDataExtractSalesOrdersP!U:U,0))"

For this example , A81 is "Common Securitization Solutions" but the name in the destination report is "Common Securitization Solution". So I get an error. I can't bring in the text I need to populate the report.

The text I am need to bring to be brought into this cell is "23Q!-053".

I changed the 0 in the March function to 1 (is this the relative match argument) but it pulled the wrong skew # and a different company name.

The yellow coded items is where the formula is located. If I find a match with the customers the result would be 23Q1-053. Yellow is my notation that that cell is hard - coded and not formula driven.

Thank you fo your help in advance.,

Michael

 

[KRice]

Have you considered the Fuzzy Lookup Add-in for Excel that Microsoft offers?

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've set up a small source table (Table 2) with some data. Then in Table 1, I have some items listed in Column1 that vary somewhat in spelling, punctuation, etc. These need to be in official Table form (select cell in range to convert and hit Ctrl-t). Then the Fuzzy Lookup add-in requires that you specify which columns to fuzzy match and which to exact match, and which columns to return. In this case, I indicated that Col 1 of Table 1 and Col 2 of Table 2 should be fuzzy matched...and Col1 Table 1 & Col 1 Table 2 should be output. Just be sure to click in an empty region of a worksheet before executing "Go" as the Output table is automatically spilled. You can also dial in different "similarity thresholds". You may be able to use the output table directly, or if necessary, use it as an intermediate translation or mapping table, where you could now use a conventional INDEX/MATCH (or XMATCH) or XLOOKUP construction to return the desired value from the intermediate table. This way the fuzzy matching, which is more sophisticated than a wildcard match, handles the initial step and you can inspect the intermediate table (perhaps you'd want to return both fuzzy match columns in that table for a direct side-by-side comparison) before relying on it.

MrExcel_20220529_v2.xlsx
	A	B	C	D	E	F
70					Table2
71					Column1	Column2
72					347-abc	Common Securities Inc.
73					23QI-053	Common Securitization Solutions
74					123-xyz	Common Market Securities
75					9999	Acme Corp
76
77	Table1			Output
78	Column1	Column2		Column1	Column1	Similarity
79	Common Securitization Solution	23QI-053		Common Securitization Solution	23QI-053	0.9408
80	Acme Corp.	9999		Acme Corp.	9999	0.9333
81	Common Securities	347-abc		Common Securities	347-abc	0.8800
82		0				0.0000
83		0				0.0000
84		0				0.0000
85		0				0.0000
86		0				0.0000
87		0				0.0000
88		0				0.0000
89		0				0.0000
Sheet1

Cell Formulas
Range	Formula
B79:B89	B79	=XLOOKUP([@Column1],$D$79:$D$89,$E$79:$E$89)

 

[bearcub]

Thank you for the information. How do I load the add in (where is the file located normally have the download)?

 

[KRice]

You can download the file at the link in my post. There are installation instructions at that same link. They describe running Setup.exe, which is the name of the downloaded file. That Setup routine will install the Add-In. You will need to restart Excel for the add-in to be recognized, then you should have a "Fuzzy Lookup" option on the top menu bar where File, Home, Insert, Draw, etc. appear in Excel. Clicking on that menu item opens up a submenu where Fuzzy Lookup appears again. Click it and the window shown in my last post appears where you can specify the column matching details.