lookup formulas to obtain multiple results

[Scott Zane]

I'm trying to figure out a way to use any of the Lookup formulas to
produce ALL matches that meet a specified criteria. To give you a bit of
an idea what I'm trying to do, here is an example below:


A B
1 Name Residence
2 Adam Los Angeles
3 Bob Costa Mesa
4 Charlie Anaheim
5 Doug Los Angeles
6 Earl Fullerton
7 Frank Santa Monica
8 George Los Angeles
9 Hank Burbank
10 Jeff Los Angeles

In the example above, I have a list of 10 people, 4 of whom reside in
"Los Angeles". If I want to setup a column on another worksheet that
lists everybody who lives in a specified city (in this case, "Los
Angeles"), how do I go about making the result look like the following
on the new worksheet?

A
1 Name
2 Adam
3 Doug
4 George
5 Jeff

I know how to use the MATCH formula to find "Jeff" when using "Los
Angeles" as the Lookup_Value, but I can't figure out how to make
subsequent rows show the next person in the list who matches that
criteria (I want somebody NOT named "Jeff" to show up instead).

I've Googled as well as sifted through over 3 years worth of posts
directly in my newsreader, and couldn't find anything that really helped
me out.

I'm still new at Visual Basic, but if I have to create a VB Macro to
produce the results I want, I'm willing to take a stab at it. Otherwise,
a simple (or not so simple) formula that I can just enter in a
particular cell would be ideal.

Thanks for any help you guys can provide!


Scott

 

[Domenic]

Assuming that A2:B10 contains the data, let D2 contain the city of
interest, such as Los Angeles. Then try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF(B$2:B$10,D$2),INDEX(A$2:A$10,SMALL(IF(B$2:B$10=
D$2,ROW(B$2:B$10)-ROW(B$2)+1),ROWS(E$2:E2))),"")

 

[CLR]

One way might be to just use the Data > Filter > AutoFilter feature.........

Vaya con Dios,
Chuck, CABGx3

 

[Ashish Mathur]

Hi Scott,

How are you? Please find a solution to a similar problem on my website -
http://ashishmathur.com/replies.aspx. Please navigae to question 2. You may
have to tweak the formula a bit. If you face any problem, please feel free
to contat me through my website.

Please feel to pass on the link of my website (www.ashishmathur.com) to as
many people.

Regards,

Ashish Mathur
Excel MVP
Visit me at www.ashishmathur.com

 

[Biff]

Hi Ashish!

Here is a link to a modified version of your sample file:
multiple_results.xls

multiple_results(modified).xls 21.0 kb

http://cjoint.com/?hkdJZirhGq

The formula I have used is significantly shorter and is more efficient. I
would highly recommend you adopt this method.

Biff

 

[Scott Zane]

I was aware of that method, but when I'm dealing with a very large list
of several thousand entries, it would be easier to just type in what I'm
looking for and let Excel spit out everything that matches up with it.

Thanks for the suggestion though.


Scott

 

[Scott Zane]

Hi Ashish!

Here is a link to a modified version of your sample file:
multiple_results.xls

multiple_results(modified).xls 21.0 kb

http://cjoint.com/?hkdJZirhGq

The formula I have used is significantly shorter and is more
efficient. I would highly recommend you adopt this method.

Biff

{=IF(ROWS($1:2)<=COUNTIF(A$3:A$9,A$11),INDEX(B$3:B$9,SMALL(IF(A$3:A$9=A
$11,ROW(B$3:B$9)-ROW(B$3)+1),ROWS($1:2))),"")}

Prior to checking back, I did manage to find a 3yr old post to another
Excel group that answered my question. However, I will definitely say that
your method is much shorter and involves one less cell than what I got from
that old post.

Even with my photographic memory, I still wonder how in the world I'm going
to remember how to make the above formula work with future worksheet
designs. So I'd better do the smart thing and save your post (and modified
version of Ashish's file) for future reference.

Thanks a bunch to all of you for the quick and helpful responses.



Scott