Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
Hi,
Im using Lookup to find a date in one column then return a value in the same row but different column: =LOOKUP(DATE(2007,10,10),$K$64,$B$64) This works well but my problem is that I have the same instance of the same date in the same column but the return value is different every time....e.g Column B Column K White 2007/10/10 Red 2007/10/9 Blue 2007/10/10 Black 2007/09/5 I want to be able to view for instance White & Red in A1:A2 for instance. I may have 20 instances of the correct date so I have to return 20 results in different rows of the same column. How can I set up the 20 Row cells to individually pick up a result if it exists. The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. I hope someone can help with this as I cant find anything that helps. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
One thought - since it's all happening in the same sheet, easier just to
apply & use autofilter on col K's dates and read off the filter results in col B. Insert a top row for col headers (if necessary) Then select entire col K, click Data Filter Autofilter. In the autofilter dropdown in K1, just select the desired date, and read off col B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Quovardis via OfficeKB.com" <u38145@uwe wrote in message news:798f9f55e2f2f@uwe... Hi, Im using Lookup to find a date in one column then return a value in the same row but different column: =LOOKUP(DATE(2007,10,10),$K$64,$B$64) This works well but my problem is that I have the same instance of the same date in the same column but the return value is different every time....e.g Column B Column K White 2007/10/10 Red 2007/10/9 Blue 2007/10/10 Black 2007/09/5 I want to be able to view for instance White & Red in A1:A2 for instance. I may have 20 instances of the correct date so I have to return 20 results in different rows of the same column. How can I set up the 20 Row cells to individually pick up a result if it exists. The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. I hope someone can help with this as I cant find anything that helps. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
HI Max,
Thanks for that, however I can filter columns due to the fact hat the info is going out in a report. This means that I have to return the data in a certain area. Max wrote: One thought - since it's all happening in the same sheet, easier just to apply & use autofilter on col K's dates and read off the filter results in col B. Insert a top row for col headers (if necessary) Then select entire col K, click Data Filter Autofilter. In the autofilter dropdown in K1, just select the desired date, and read off col B Hi, [quoted text clipped - 30 lines] I hope someone can help with this as I cant find anything that helps. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
Sorry...meant cant
Quovardis wrote: HI Max, Thanks for that, however I can filter columns due to the fact hat the info is going out in a report. This means that I have to return the data in a certain area. One thought - since it's all happening in the same sheet, easier just to apply & use autofilter on col K's dates and read off the filter results in [quoted text clipped - 9 lines] I hope someone can help with this as I cant find anything that helps. -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
One way...
Enter the date of interest in cell A1. Assume your data is in the range B2:B110 and K2:K100. Enter this array formula** in cell A2 and copy down until you get blanks: =IF(ROWS(A$2:A2)<=COUNTIF(K$2:K$100,A$1),INDEX(B$2 :B$100,SMALL(IF(K$2:K$100=A$1,ROW(B$2:B$100)-MIN(ROW(B$2))+1),ROWS(B$2:B2))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. You'd have to rely on past knowledge. For example, if you know the most matching dates that there has ever been is say, 30, then you have to copy the formula to at least 30 cells. -- Biff Microsoft Excel MVP "Quovardis via OfficeKB.com" <u38145@uwe wrote in message news:798f9f55e2f2f@uwe... Hi, Im using Lookup to find a date in one column then return a value in the same row but different column: =LOOKUP(DATE(2007,10,10),$K$64,$B$64) This works well but my problem is that I have the same instance of the same date in the same column but the return value is different every time....e.g Column B Column K White 2007/10/10 Red 2007/10/9 Blue 2007/10/10 Black 2007/09/5 I want to be able to view for instance White & Red in A1:A2 for instance. I may have 20 instances of the correct date so I have to return 20 results in different rows of the same column. How can I set up the 20 Row cells to individually pick up a result if it exists. The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. I hope someone can help with this as I cant find anything that helps. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
Typo:
Assume your data is in the range B2:B110 and K2:K100. Should be: Assume your data is in the range B2:B100 and K2:K100. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... Enter the date of interest in cell A1. Assume your data is in the range B2:B110 and K2:K100. Enter this array formula** in cell A2 and copy down until you get blanks: =IF(ROWS(A$2:A2)<=COUNTIF(K$2:K$100,A$1),INDEX(B$2 :B$100,SMALL(IF(K$2:K$100=A$1,ROW(B$2:B$100)-MIN(ROW(B$2))+1),ROWS(B$2:B2))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. You'd have to rely on past knowledge. For example, if you know the most matching dates that there has ever been is say, 30, then you have to copy the formula to at least 30 cells. -- Biff Microsoft Excel MVP "Quovardis via OfficeKB.com" <u38145@uwe wrote in message news:798f9f55e2f2f@uwe... Hi, Im using Lookup to find a date in one column then return a value in the same row but different column: =LOOKUP(DATE(2007,10,10),$K$64,$B$64) This works well but my problem is that I have the same instance of the same date in the same column but the return value is different every time....e.g Column B Column K White 2007/10/10 Red 2007/10/9 Blue 2007/10/10 Black 2007/09/5 I want to be able to view for instance White & Red in A1:A2 for instance. I may have 20 instances of the correct date so I have to return 20 results in different rows of the same column. How can I set up the 20 Row cells to individually pick up a result if it exists. The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. I hope someone can help with this as I cant find anything that helps. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200710/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
Dude...your a genius.
It works perfectly....thank you sooo much T. Valko wrote: One way... Enter the date of interest in cell A1. Assume your data is in the range B2:B110 and K2:K100. Enter this array formula** in cell A2 and copy down until you get blanks: =IF(ROWS(A$2:A2)<=COUNTIF(K$2:K$100,A$1),INDEX(B$ 2:B$100,SMALL(IF(K$2:K$100=A$1,ROW(B$2:B$100)-MIN(ROW(B$2))+1),ROWS(B$2:B2))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. You'd have to rely on past knowledge. For example, if you know the most matching dates that there has ever been is say, 30, then you have to copy the formula to at least 30 cells. Hi, [quoted text clipped - 30 lines] I hope someone can help with this as I cant find anything that helps. -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup with Multiple results
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Quovardis via OfficeKB.com" <u38145@uwe wrote in message news:7990723733ae4@uwe... Dude...your a genius. It works perfectly....thank you sooo much T. Valko wrote: One way... Enter the date of interest in cell A1. Assume your data is in the range B2:B110 and K2:K100. Enter this array formula** in cell A2 and copy down until you get blanks: =IF(ROWS(A$2:A2)<=COUNTIF(K$2:K$100,A$1),INDEX(B $2:B$100,SMALL(IF(K$2:K$100=A$1,ROW(B$2:B$100)-MIN(ROW(B$2))+1),ROWS(B$2:B2))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) The other big problem comes because the data comes from an updatable DB and when I extract the data i dont know exactly how many matching dates im going to have. You'd have to rely on past knowledge. For example, if you know the most matching dates that there has ever been is say, 30, then you have to copy the formula to at least 30 cells. Hi, [quoted text clipped - 30 lines] I hope someone can help with this as I cant find anything that helps. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
single lookup value with multiple results | Excel Worksheet Functions | |||
Lookup multiple results with gaps | Excel Worksheet Functions | |||
Lookup Multiple results has gaps | Excel Worksheet Functions | |||
Lookup Returning Multiple Results | Excel Discussion (Misc queries) | |||
How can I do a lookup and get multiple row results? | Excel Worksheet Functions |