I have dataset sorted by date/time, with 4 columns of data. (Table A)
I have another table with dates that I'm interested in. (Table B)

I need to use Table B to look in Table A and return the maximum value of a range associated with the date from Table A which is specified by Table B.

Example:

Table A

Col A                Col B     Col C      Col D    Col E
01/01/10 0:00          1          1         0         1
01/01/10 12:00         2          2         3         2
01/02/10 0:00          0          0         0         0
01/02/10 12:00         3          1         2         1
01/03/10 0:00          0          0         0         0
01/03/10 12:00         3          5         2         1

Col A           Col B      
01/01/10       Max of 01/01/10 data from Cols B - E (thus, 3)
01/03/10       Max of 01/03/10 data from Cols B - E (thus, 5)

11/14/2010 10:07:10 PM

just ended up doing a pivot table for now; though a cell solution would be nice to have.

11/14/2010 11:30:57 PM

This would be super easy to do in access. Not that that helps you any.

11/14/2010 11:49:31 PM

So the number of rows per date are different? If they are the same the MAX function would work easy enough with cell references.

11/15/2010 12:11:47 AM

^eh, yeh since there are different intervals. and the max function is the obvious part. the more difficult part is getting the max function to look at a range for a specified day, among 400 days. there are 90,000+ rows of data here, so some convoluted formula isn't going to work. so I don't really see what you are suggesting... to just manually do a max for each day? that is not at all what I am asking for.

[Edited on November 15, 2010 at 8:28 AM. Reason : .]

11/15/2010 8:24:27 AM

oh table b doesn't have a row for every day, i missed that.

i'd use pivot tables too

11/15/2010 9:49:25 AM

haha, yeh. I don't think a single cell solution is that straight forward

and the pivot table worked well; just time consuming to filter out the dates I wasn't interested in.

and sorry if I sounded dickish in ^^... I was still half asleep.

11/15/2010 10:59:00 AM