Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
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))),"") In article 1, 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
One way might be to just use the Data Filter AutoFilter feature.........
Vaya con Dios, Chuck, CABGx3 "Scott Zane" wrote in message 7.131... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
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 "CLR" wrote in : One way might be to just use the Data Filter AutoFilter feature......... Vaya con Dios, Chuck, CABGx3 "Scott Zane" wrote in message 7.131... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formulas to obtain multiple results
"Biff" wrote in
: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup with multiple results, without duplicates | Excel Worksheet Functions | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
Lookup Tables and Formulas | Excel Worksheet Functions | |||
Lookup Returning Multiple Results | Excel Discussion (Misc queries) | |||
Displaying the results of multiple formulas in a single cell. | New Users to Excel |