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
"Ashish Mathur" wrote in message
...
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
"Scott Zane" wrote:
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