|
|
|
date: Wed, 27 Aug 2008 07:46:01 -0700,
group: microsoft.public.excel.worksheet.functions
back
RE: 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,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
>
>
>
date: Wed, 27 Aug 2008 08:20:01 -0700
author: Teethless mama
RE: lookups
Another...
=INDEX(clients!$B$1:$B$200,MATCH("*"&A1&"*",clients!$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,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
>
>
>
date: Wed, 27 Aug 2008 08:28:02 -0700
author: Teethless mama
RE: 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,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
> >
> >
> >
date: Wed, 27 Aug 2008 09:04:03 -0700
author: JoviGirl
RE: 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,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
> > >
> > >
> > >
date: Wed, 27 Aug 2008 09:32:08 -0700
author: Teethless mama
|
|
