Lookup formula of some sort

[lhkittle]

I offered a VBA solution to a poster in another forum which works very well given the "Yipieeeeeee... it works" response I got back.

Poster has zero vb knowledge and even with the workable macro insists on a formula to do the same, for which I am at a loss.

Column A has a list of names which repeat, about 12 names w/ three unique.
Column B has a list of names, all unique.

With one of the unique names from Column A in F1 is there a formula that will list the names adjacent to the name in F1 from the list in column B.

Joe Martin
Silvia Jacob
Nathan Lewis
Joe Samantha
Silvia Jerry
Nathan Britney
Joe Sheila
Silvia Roger
Nathan Randolph
Joe Cherry
Silvia Jerrard
Nathan Monica

So if Joe is in F1 the list should be:

Martin
Samantha
Sheila
Cherry

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 8 Jul 2013 03:37:56 -0700 (PDT) schrieb (e-mail address removed):

Joe Martin
Silvia Jacob
Nathan Lewis
Joe Samantha
Silvia Jerry
Nathan Britney
Joe Sheila
Silvia Roger
Nathan Randolph
Joe Cherry
Silvia Jerrard
Nathan Monica

So if Joe is in F1 the list should be:

Martin
Samantha
Sheila
Cherry

try in G1:
=INDEX(B:B,SMALL(IF(A$1:A$100=$F$1,ROW($1:$100)),ROW(A1)))
and enter the array formula with CTRl+Shift+Enter and copy down till you
get an error


Regards
Claus B.

 

[lhkittle]

Hi Howard,



Am Mon, 8 Jul 2013 03:37:56 -0700 (PDT) schrieb (e-mail address removed):






try in G1:

=INDEX(B:B,SMALL(IF(A$1:A$100=$F$1,ROW($1:$100)),ROW(A1)))

and enter the array formula with CTRl+Shift+Enter and copy down till you

get an error

Regards

Claus B.

Thanks Claus, I will pass this on with credit to you.
Works pretty nice.

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 8 Jul 2013 04:43:23 -0700 (PDT) schrieb (e-mail address removed):

Works pretty nice.

what about autofilter? No need for formula or VBA


Regards
Claus B.

 

[lhkittle]

Hi Howard,



Am Mon, 8 Jul 2013 04:43:23 -0700 (PDT) schrieb (e-mail address removed):






what about autofilter? No need for formula or VBA





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

That would most likely work, in light of the fact you mention it. I will toil with that and perhaps offer it up.

Thanks.
Howard

 

[AndyHolloway]

I offered a VBA solution to a poster in another forum which works ver
well given the "Yipieeeeeee... it works" response I got back.

Poster has zero vb knowledge and even with the workable macro insists o
a formula to do the same, for which I am at a loss.

Column A has a list of names which repeat, about 12 names w/ thre
unique.
Column B has a list of names, all unique.

With one of the unique names from Column A in F1 is there a formula tha
will list the names adjacent to the name in F1 from the list in colum
B.

Joe Martin
Silvia Jacob
Nathan Lewis
Joe Samantha
Silvia Jerry
Nathan Britney
Joe Sheila
Silvia Roger
Nathan Randolph
Joe Cherry
Silvia Jerrard
Nathan Monica

So if Joe is in F1 the list should be:

Martin
Samantha
Sheila
Cherry

Regards,
Howard

Looks ok but need to give to one try for sure.. Hope it works wel