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

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

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

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