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

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!!!!!!

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.

Please Login or Register  to view this content.

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.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.