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)
Adam Thwaites wrote:
Thanks, both formula work, but what do I have to change to retrieve the 1st
value from the table instead of the last?
--
Adam Thwaites
Access Database Designer
Manchester, UK
"Dave Peterson" wrote:
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 wrote:
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!
--
Adam Thwaites
Access Database Designer
Manchester, UK
--
Dave Peterson
--
Dave Peterson