Lookup formula of some sort

  • Thread starter Thread starter lhkittle
  • Start date Start date
L

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
 
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
Click to expand...

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.
 
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
Click to expand...
Regards

Claus B.
Click to expand...

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

Regards,
Howard
 
Hi Howard,

Am Mon, 8 Jul 2013 04:43:23 -0700 (PDT) schrieb (e-mail address removed):
Works pretty nice.
Click to expand...

what about autofilter? No need for formula or VBA


Regards
Claus B.
 
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
Click to expand...

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
 
Back
Top