Another multiple criteria/column question

  • Thread starter Thread starter snobordr
  • Start date Start date
S

snobordr

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.
 
=INDEX(Sheet2!$D$2:$D$20,MATCH(1,(Sheet1!$A2=Sheet2!$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=Sheet2!$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=Sheet2!$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)
 
Ok, thanks for the help and I get the concept now. What I don't get ar
the results that I should from using these formula. The column I a
referencing for the time filter is retrieved from a MS SQL database an
comes in their datetime format. The minute ><= functionality is no
working correctly and I am wondering if this is due to the format fro
SQL.

Thanks again. ;
 
Can you give an example.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Back
Top