Excel 2007 : Partial Match, Multiple Criteria PLEASE HELP!!!

**Dan.carpe@gmail.com** · 02-03-2012, 10:58 AM

I am trying to do the following:

COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F
Airline 1 Belgium Arline1 Belg. EUR
Airlinee 2 Switzerland Airline2 Switzlan EUR
Airline 3 Morrocco Aiiline 3 Morocco USD
etc.
etc.
etc.

I need to partially match COLUMN A to COLUMN C, as well as partially match COLUMN B to COLUMN D. If BOTH criteria are met, then yield the same value from COLUMN E into COLUMN F

Please help I have tried EVERYTHING!!!!!!

**MarvinP** · 02-03-2012, 11:39 AM

Hi Dan and welcome to the forum,

I believe you are looking for a Fuzzy Logic type of matching. I don't have a good answer but perhaps knowing the term will help.

**JBeaucaire** · 02-03-2012, 12:01 PM

COLUMN A    COLUMN B    COLUMN C    COLUMN D    COLUMN E    COLUMN F
Airline 1       Belgium        Arline1         Belg.           EUR
Airlinee 2     Switzerland   Airline2        Switzlan       EUR
Airline 3       Morrocco      Aiiline 3       Morocco       USD
etc.
etc.
etc.

When attempting to apply crazy fuzzy matching like this, the human eye can provide context a program cannot. Worse, you don't have ONE column with "always clean" answers and one with errors, both columns are full of errors.

You will need create your own reference table to lookup corrected spellings... the first column you gather all the misspellings (and one copy of the correct spellings), and in the second column you put the correct spellings for each. It's a chore to put together, but when done you would be able to reuse that table, and only when you column F formula fails and gives an error would you add to the table of errors to get the new variations in.

I've attached an example, just keep adding to the lookup table. As it grows, more and more of your rows will resolve themselves. The formula is in G1 already.

The results you will get are:
1) The number from E will appear because you lookup table has provided matching strings
2) #VALUE will appear because your strings are not yet in the table, add them
3) BLANK will appear because all strings are in the table and they do not match.

**Dan.carpe@gmail.com** · 02-06-2012, 06:01 AM

Thanks to both of you for the quick response!

Jerry, your answer was particularly helpful thank you!!

Although this does work well, I am trying to avoid manually sifting through partially matched data and correcting mistakes.

There must be some formula / algorythim that allows for this (similar to Google search ie. Tyger Wodes will bring up Tiger Woods automatically)...

Maybe even some VBA or C++ integrated code will work??

Every problem has a solution and we shall find it!!

Until then...

**Dan.carpe@gmail.com** · 02-14-2012, 06:18 AM

!!! SOLVED !!!

To Jerry and Marvin thank you for your assistance, Marvin for pointing out the Fuzzy Lookup function.

The answer is to download the Fuzzy Lookup addin for Excel on the Microsoft website.

Once installed it will appear as an addin in Excel and runs like a little micro function without formulas.

Exactly what I needed and I hope this will help anybody else out there with the same problem.

Regards,

Dan