Lookup formula of some sort
 

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

Lookup formula of some sort
 

Hi Howard,

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

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)),R OW(A1)))
and enter the array formula with CTRl+Shift+Enter and copy down till you
get an error


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Lookup formula of some sort
 

On Monday, July 8, 2013 4:14:14 AM UTC-7, Claus Busch wrote:
Hi Howard,



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



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)),R OW(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

Lookup formula of some sort
 

Hi Howard,

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

Works pretty nice.

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

Lookup formula of some sort
 

On Monday, July 8, 2013 5:32:55 AM UTC-7, Claus Busch wrote:
Hi Howard,



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



Works pretty nice.



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

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