Fuzzy Matching - new version plus explanation

[al_b_cnu]

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:

Fuzzy Examples.xls
	A	B	C	D	E
1			Starts With	Ends	Contains
2			Bill	jelen	Bill
3	Mr Bill Jelen		4	3	3
4	Bill Jelen
5	Joe Bloggs
6	Fred Smith
MATCH Example

... Continued ...

 

[al_b_cnu]

Hi, I am trying to match a short description to a different set of data/descriptions. The descriptions I am trying to compare to are not exactly the same. Some may be longer or shorter and/or have extra/less words. Once I match the values, i need to get a value that has a "table location" in a different column. Is there a way to pull up the "table location" value as well as the result from the algorithm?

Hi,

Try setting the 'IndexNum' value to 0 - it will return the best match row number.

 

[RFisher]

Hi,

Try setting the 'IndexNum' value to 0 - it will return the best match row number.

Probably a dumb question, but where would i change that

Also, is there a way to have the fuzzyvlookup look at multiple words in the lookup value cell AND the lookup table cells? Because some of my values have the same first word, but different words after that. For example, Amputation of the Arm, Amputation of the Finger. All pull up the same value, which is neither Arm or Finger lol.

 

[al_b_cnu]

Not really, I think you'll have to write a bespoke UDF.

Unfortunately (for you thsat is), I'm off to somewhere far sunnier than manchester for a week as of 4am tomorrow morning, so unless you can wait a week, suggest you post a new thread.
Failing that, by all means PM me & I'll see what we can come up with

 

[RFisher]

Not really, I think you'll have to write a bespoke UDF.

Unfortunately (for you thsat is), I'm off to somewhere far sunnier than manchester for a week as of 4am tomorrow morning, so unless you can wait a week, suggest you post a new thread.
Failing that, by all means PM me & I'll see what we can come up with

Take me with you? I will post a new thread and see what they come up with and if that doesnt work, I will PM you. Because i dont know what a bespoke UDF is lol.

 

[al_b_cnu]

Hi,

If still problems, pm me on 6th

 

[RFisher]

can anyone explain how to use the 'group' parameter? I cant figure it out

 

[RFisher]

Hi Alan, I am trying to use the fuzzyvlookup for another project I'm working on! This should be relatively simple, I just need to match names of conference attendees to a master roster. When the attendees sign into the conference, they don't always write their name the way it is in the roster. In the roster there are fields that I need to pull (ie ID number, department, department section, etc). How could I make the fuzzylookup match the name from the attendance list to the name in the roster?

 

[al_b_cnu]

Hi,

Try using algorithm 1 which is best suited for matching misspollings

 

[RFisher]

I keep getting the #NAME? error. When i look at the arguments in the function, it is showing the lookup value as the #value! error. Do the columns have to be set up a specific way? Also, the main roster I am using as the lookup table is in a separate workbook. Does that matter?

 

[al_b_cnu]

I keep getting the #NAME? error. When i look at the arguments in the function, it is showing the lookup value as the #value! error. Do the columns have to be set up a specific way? Also, the main roster I am using as the lookup table is in a separate workbook. Does that matter?

Hi,

if the columns are set up as you would do so for the inbuilt VLOOKUP, you should be ok.
For this version of FuzzyVLookup, the lookup table must be in the same workbook as the module.