Lookup to find the last value based on name and date field
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 |
Lookup to find the last value based on name and date field
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Adam Thwaites" wrote in message ... 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 |
Lookup to find the last value based on name and date field
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 |
Lookup to find the last value based on name and date field
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 |
Lookup to find the last value based on name and date field
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 |
All times are GMT +1. The time now is 11:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com