lookups

[JoviGirl]

I am trying to compare 2 different listings to see if any in list 1 is a
client. My lookup will only return the client if the client is an exact
match. How can I make the lookup return any variation of the name

This is my normal lookup
=IF(ISERROR(VLOOKUP(A1,'clients'!$A$1:$B$200,2,FALSE)),0,VLOOKUP(A1,'clients'!$A$1:$B$200,2,FALSE))

Example
Sheet 1 - (Possible Client Names)

Column A
ABC Co

Sheet 2 - (our client names)

Column A Col B
The ABC Company 1000

So I want to look up the name in sheet 1 and return the possible match from
sheet 2.

Thanks

 

[Teethless mama]

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,clients!A1:A200))),clients!B1:B200)

 

[Teethless mama]

Another...

=INDEX(clients!$B$1:$B$200,MATCH("*"&A1&"*",clients!$A$1:$A$200,0))

 

[JoviGirl]

Thanks ... but it looks like it isnt picking up everything...
in the possible client names, one in particular is "The Charlie Brown
Corporation" and we have a client called "Charlie Brown & Co" that should be
a match. Anyway to accomplish this? Thanks for the help

 

[Teethless mama]

"The Charlie Brown Corporation"

"Charlie Brown & Co"

"Kentucky Fried Chicken"
"KFC"

We all know the examples above should match, but not for EXCEL