Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookups
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookups | Excel Worksheet Functions | |||
Lookups or something | Excel Worksheet Functions | |||
Maybe I need help with Lookups?? | Excel Worksheet Functions | |||
Lookups | Excel Worksheet Functions | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |