Comparing oddly named values between two tables

**Jimboted** · 09-02-2010, 11:22 AM

Hi All,

I have been tasked to compare two separate tables of mobile call rates and highlight any matches in tariff (e.g. Table A & Table B contain a call rate for AWC mobile in afghanistan and that rate is....). However the data is not named at all well. I have tried striping some information out and using VLOOKUP to compare, but am getting nowhere fast.

Would really appreciate it if someone can offer advice on how to do this. If there is no way to look for partial matches etc I will be faced with manually checking over a thousand call destinations again a hundred suppliers - which will take until the far side of forever to finish!

Two pictures attached, the first shows the data as it is in the spreadsheet, the second shows the desired outcome.

Any queries please let me know.

Thanks,

Jim

**NBVC** · 09-02-2010, 11:42 AM

Try something like:

=INDEX($B$2:$B$10,MATCH(D2&"*"&E2,$A$2:$A$10,0))

assuming your data is a2:A10

**Jimboted** · 09-03-2010, 06:04 AM

NBVC, that is an elegant solution to my problem, thank you very much for your assistance with this (has also spurred me to finally learn to use MATCH and INDEX correctly!).

It returns the results correctly, the only issue is that it matches "Afghanistan At" with "Afghanistan Etisalat". Clearly this is because of the last two letters. It is not the end of the world if this doesn't match, but can you suggest an clever finesses to get over this very minor problem?

Many thanks,

Jim

**NBVC** · 09-03-2010, 07:48 AM

Perehaps:

=INDEX($B$2:$B$10,MATCH(D2&"*"&" "&E2,$A$2:$A$10,0))

**Jimboted** · 09-03-2010, 08:03 AM

Outstanding, thank you very much for your assistance.

Have a good weekend,

Jim

**NBVC** · 09-03-2010, 08:10 AM

You are welcome

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save