Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLOOKUP(B2,'Sheet2'!,A:A,4) WHat I need to do is: For Sheet1, column C: Find the value (on Sheet2) with a timestamp in between minute 00 and minute 19 For Sheet1, column D: Find the value on Sheet2 with a timestamp between minute 20 and minute 39 For Sheet1, column E: Find the value on Sheet2 with a timestamp between minute 40 and minute 59 Ok, it may not even make sense, but as I said, learning Excel here so a lot of this is completely foreign to me. -- snobordr ------------------------------------------------------------------------ snobordr's Profile: http://www.excelforum.com/member.php...o&userid=36075 View this thread: http://www.excelforum.com/showthread...hreadid=558617 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Another multiple criteria/column question
=INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet 2!$A$2:$A$20)*
(MINUTE(Sheet2!$D$2:$D$20)=0)*(MINUTE(Sheet2!$D$2 :$D$20)<20),0)) =INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet 2!$A$2:$A$20)* (MINUTE(Sheet2!$D$2:$D$20)=20)*(MINUTE(Sheet2!$D$ 2:$D$20)<40),0)) =INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet 2!$A$2:$A$20)* (MINUTE(Sheet2!$D$2:$D$20)=40)*(MINUTE(Sheet2!$D$ 2:$D$20)<=60),0)) which are array formulae, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "snobordr" wrote in message ... Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLOOKUP(B2,'Sheet2'!,A:A,4) WHat I need to do is: For Sheet1, column C: Find the value (on Sheet2) with a timestamp in between minute 00 and minute 19 For Sheet1, column D: Find the value on Sheet2 with a timestamp between minute 20 and minute 39 For Sheet1, column E: Find the value on Sheet2 with a timestamp between minute 40 and minute 59 Ok, it may not even make sense, but as I said, learning Excel here so a lot of this is completely foreign to me. -- snobordr ------------------------------------------------------------------------ snobordr's Profile: http://www.excelforum.com/member.php...o&userid=36075 View this thread: http://www.excelforum.com/showthread...hreadid=558617 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Another multiple criteria/column question
Ok, thanks for the help and I get the concept now. What I don't get are the results that I should from using these formula. The column I am referencing for the time filter is retrieved from a MS SQL database and comes in their datetime format. The minute <= functionality is not working correctly and I am wondering if this is due to the format from SQL. Thanks again. ;) -- snobordr ------------------------------------------------------------------------ snobordr's Profile: http://www.excelforum.com/member.php...o&userid=36075 View this thread: http://www.excelforum.com/showthread...hreadid=558617 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Another multiple criteria/column question
Can you give an example.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "snobordr" wrote in message ... Ok, thanks for the help and I get the concept now. What I don't get are the results that I should from using these formula. The column I am referencing for the time filter is retrieved from a MS SQL database and comes in their datetime format. The minute <= functionality is not working correctly and I am wondering if this is due to the format from SQL. Thanks again. ;) -- snobordr ------------------------------------------------------------------------ snobordr's Profile: http://www.excelforum.com/member.php...o&userid=36075 View this thread: http://www.excelforum.com/showthread...hreadid=558617 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Vlookup against multiple columns/worksheets question | Excel Discussion (Misc queries) | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) |