VLOOKUP .. but then with multiple variables ?? Im lost

[ed_terpstra]

Issue:


i have a list(A) of 70.000 accounts and want to get a match if these accounts are also in a list (B) of more than 100 Accounts.

The probem is that there are no 'exact matches'.

Example :

List A

- McDonalds New York

- Mc Donalds Houston

- Mc Donald's SF

- Burger King Atlanta

- BurgerKing NewOrelans


List B

Mc Donald's

Burger King

etc


So, vlookup is no option .. what to do?

 

[Hui]

Ed_terpstra


Firstly, Welcome to the Chandoo.org Forums


I'd suggest you cleanup your data first

An easy way to do that is Apply a filter

Then go through the filtered list and do a Search/Replace for the odd ones like BurgerKing to Burger King


Then do your vlookup etc as normal

 

[rsk]

Hi ed_terpstra,


Try wildcard search using *search value*,


=vlookup(*Burger*, range , , )


this gives all value which contains Burger , Donalds etc..

 

[Luke M]

Another idea would be to use some sort of a "fuzzy" lookup:

http://chandoo.org/wp/2008/09/25/handling-spelling-mistakes-in-excel-fuzzy-search/