Lookup to find the last value based on name and date field

[Adam Thwaites]

Worksheet is called 'Raw Data'
Column A = Name
Column B = Date
Column D = Time

I need a lookup to pull out the first time (for one cell) and last time (for
another
cell) based on criteria in a seperate worksheet ('Report') where A1 contains
the Name to filter on, and B1 contains the Date to filter on.

I used the fomula
=LOOKUP(2,1/('Raw Data'!$A$2:$A$10000=$A$1),'Raw Data'!$D$2:$D$10000)
to find the last time based on Name, but I don't know how to add the extra
date criteria in.

Thanks in advance for your help!

 

[Bernard Liengme]

I experiment with a smaller data base, and came up with
=LOOKUP(2,1/(Sheet1!$A$2:$A$4&Sheet1!$B$2:$B$4=A1&B1),Sheet1!$D$2:$D$4)
best wishes

 

[Dave Peterson]

Maybe:

=LOOKUP(2,1/(('Raw Data'!$A$2:$A$10000=$A$1)*('Raw Data'!$B$2:$B$10000=$B$1)),
'Raw Data'!$D$2:$D$10000)

 

[Adam Thwaites]

Thanks, both formula work, but what do I have to change to retrieve the 1st
value from the table instead of the last?

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

========
So in your case:

=index('raw data'!$d$2:$d$10000,
match(1,($a$2='raw data'!$a$2:$a$10000)
*($b$2='raw data'!$b$2:$b$10000),0))

(still ctrl-shift-entered)