lookups

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,FAL SE)),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

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


"JoviGirl" wrote:

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,FAL SE)),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

Another...

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


"JoviGirl" wrote:

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,FAL SE)),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

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" wrote:

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


"JoviGirl" wrote:

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,FAL SE)),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

"The Charlie Brown Corporation"
"Charlie Brown & Co"

"Kentucky Fried Chicken"
"KFC"

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


"JoviGirl" wrote:

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" wrote:

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


"JoviGirl" wrote:

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,FAL SE)),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