Excel: Fuzzy Matching - new version plus explanation


It has been a while since I originally posted my Fuzzy matching UDF’s on the board, and several variants have appeared subsequently. I thought it time to ‘put the record straight’ & post a definitive version which contains slightly more efficient code, and better matching algorithms, so here it is. Firstly, I must state that the Fuzzy matching algorithms are very CPU hungry, and should be used sparingly. If for instance you require to lookup a match for a string which starts with, contains or ends with a specified value, this can be performed far more efficiently using the MATCH function:

Microsoft Excel - Fuzzy Examples.xls___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=
A
B
C
D
E
1
Starts WithEndsContains
2
BilljelenBill
3
Mr Bill Jelen 433
4
Bill Jelen
5
Joe Bloggs
6
Fred Smith
MATCH Example
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
... Continued ...


This question generated 28 answers. To proceed to the answers, click here.

This thread is current as of March 27, 2006.


For more resources for Microsoft Excel