Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup: Multiple Occurances
If I have a worksheet with serial number and date, how can I lookup the
entries from another sheet and display ALL the entrys. 1234 01/08/2005 1235 02/08/2005 1236 04/08/2005 1234 06/09/2005 1236 03/09/2005 1236 10/10/2005 For example on a second sheet if I look up the serial I need this sheet to display ALL entries relating to that serial? i.e 1234 01/08/2005 06/09/2005 or 1238 04/08/2005 or 1236 04/08/2005 03/09/2005 10/10/2005 etc |
#2
|
|||
|
|||
One possible way
=INDEX('another sheet'!$B$1:$B$100,SMALL(IF('another sheet'!$A$1:$A$100=$B$1,ROW('another sheet'!$A$1:$A$100)),ROW(1:1))) where B1 holds the serial number to lookup, A1:A100 the serial numbers and B1:B100 the dates enter with ctrl + shift & enter copy down until you get an error -- Regards, Peo Sjoblom (No private emails please) "Jim" <Jim wrote in message k... If I have a worksheet with serial number and date, how can I lookup the entries from another sheet and display ALL the entrys. 1234 01/08/2005 1235 02/08/2005 1236 04/08/2005 1234 06/09/2005 1236 03/09/2005 1236 10/10/2005 For example on a second sheet if I look up the serial I need this sheet to display ALL entries relating to that serial? i.e 1234 01/08/2005 06/09/2005 or 1238 04/08/2005 or 1236 04/08/2005 03/09/2005 10/10/2005 etc |
#3
|
|||
|
|||
Lookup: Multiple Occurances
Another way, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook: =VLookups(1234,A1:B100,2) array entered into a column of cells sufficient to accommodate the number of occurrences. Alan Beban Peo Sjoblom wrote: One possible way =INDEX('another sheet'!$B$1:$B$100,SMALL(IF('another sheet'!$A$1:$A$100=$B$1,ROW('another sheet'!$A$1:$A$100)),ROW(1:1))) where B1 holds the serial number to lookup, A1:A100 the serial numbers and B1:B100 the dates enter with ctrl + shift & enter copy down until you get an error |
#4
|
|||
|
|||
Lookup: Multiple Occurances
Hi,
Assuming your list is in range A1:B7, enter the serial number for whivh you want the data returned in A10. Now enter the following array formula (Ctrl+Shift+Enter) in B10 and copy downwards IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$1 0,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7 ,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ),2)) Regards, Ashish Mathur "Jim" wrote: If I have a worksheet with serial number and date, how can I lookup the entries from another sheet and display ALL the entrys. 1234 01/08/2005 1235 02/08/2005 1236 04/08/2005 1234 06/09/2005 1236 03/09/2005 1236 10/10/2005 For example on a second sheet if I look up the serial I need this sheet to display ALL entries relating to that serial? i.e 1234 01/08/2005 06/09/2005 or 1238 04/08/2005 or 1236 04/08/2005 03/09/2005 10/10/2005 etc |
#5
|
|||
|
|||
Lookup: Multiple Occurances
Hi!
A couple of pointers: IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$1 0,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX ($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7 )),ROW(1:1)),2)) There's no need to index both columns A and B since you're only interested in and going to return data from a single column. Also, you don't need to include the Index at all in the error trap. Any error generated by the formula will come from this portion: SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)) So, with those suggestions the formula would be: IF(ISERROR(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7) ),ROW(1:1))),"",INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7 =$A$10,ROW($A$1:$A$7)),ROW(1:1)))) Biff "Ashish Mathur" wrote in message ... Hi, Assuming your list is in range A1:B7, enter the serial number for whivh you want the data returned in A10. Now enter the following array formula (Ctrl+Shift+Enter) in B10 and copy downwards IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$1 0,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7 ,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1) ),2)) Regards, Ashish Mathur "Jim" wrote: If I have a worksheet with serial number and date, how can I lookup the entries from another sheet and display ALL the entrys. 1234 01/08/2005 1235 02/08/2005 1236 04/08/2005 1234 06/09/2005 1236 03/09/2005 1236 10/10/2005 For example on a second sheet if I look up the serial I need this sheet to display ALL entries relating to that serial? i.e 1234 01/08/2005 06/09/2005 or 1238 04/08/2005 or 1236 04/08/2005 03/09/2005 10/10/2005 etc |
#6
|
|||
|
|||
Lookup: Multiple Occurances
You need to replace ROW(1:1) with something else for robustness. The
idiom is not efficient when the range to process is very large, a point I felt to point out at some occasions as in http://tinyurl.com/b6zk7. Biff wrote: Hi! A couple of pointers: IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A $10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX ($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$ 7)),ROW(1:1)),2)) There's no need to index both columns A and B since you're only interested in and going to return data from a single column. Also, you don't need to include the Index at all in the error trap. Any error generated by the formula will come from this portion: SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)) So, with those suggestions the formula would be: IF(ISERROR(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7) ),ROW(1:1))),"",INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7 =$A$10,ROW($A$1:$A$7)),ROW(1:1)))) Biff "Ashish Mathur" wrote in message ... Hi, Assuming your list is in range A1:B7, enter the serial number for whivh you want the data returned in A10. Now enter the following array formula (Ctrl+Shift+Enter) in B10 and copy downwards IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A $10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B $7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1: 1)),2)) Regards, Ashish Mathur "Jim" wrote: If I have a worksheet with serial number and date, how can I lookup the entries from another sheet and display ALL the entrys. 1234 01/08/2005 1235 02/08/2005 1236 04/08/2005 1234 06/09/2005 1236 03/09/2005 1236 10/10/2005 For example on a second sheet if I look up the serial I need this sheet to display ALL entries relating to that serial? i.e 1234 01/08/2005 06/09/2005 or 1238 04/08/2005 or 1236 04/08/2005 03/09/2005 10/10/2005 etc -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Lookup on Multiple Criteria | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
Return Multiple Results with Lookup | Excel Worksheet Functions |