Max if between 2 dates?

J

John

I have dates in column A & B. They correspond to dates in column A of
sheet2... In column C (of sheet1) I would like to find the max value found on
Sheet2 in column B corresponding/between the two dates of cloumn A&B on
sheet1. Is that make sense?

Thanks for your cunning thoughts!
 
B

Bob Phillips

=MAX(INDEX((Sheet2!$A$2:$A$10>=A2)*(Sheet2!$A$2:$A$10<=B2)*(Sheet2!$B$2:$B$10),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

John

=MAX(INDEX('data FI'!$T$3:$X$1000,('data FI'!$T$3:$T$1000>=W2)*('data
FI'!$T$3:$T$1000<=T2)*('data FI'!$V$3:$V$1000),0))

AND

=MAX(INDEX(('data FI'!$T$3:$T$1000>=W2)*('data FI'!$T$3:$T$1000<=T2)*('data
FI'!$V$3:$V$1000),0))

For my sheet is not working

I will keep playing around with it, but any thoughts?
 
J

John

And I tried =MAX(OFFSET(INDEX(('data FI'!$T$3:$T$1000>=W2)*('data
FI'!$T$3:$T$1000<=T2),1),0,2)) which doesn't work either :)
 
B

Bob Phillips

You can't use two columns as per your first example, and you cannot add a
third index argument as per your third. Your second one works fine in my
tests though.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

John

SO =MAX(INDEX(('data FI'!$T$3:$T$1000>=W16)*('data
FI'!$T$3:$T$1000<=T16)*('data FI'!$V$3:$V$1000),0))

so this Finds the MAX in column V on the sheet 'data FI' between the dates
given in W16 and T16???.... so the index may find 5 dates between the 2
values and therfore 5 values in column V and takes the MAX of those 5
values???? ... it doesn't work for me. Can you send your sheet to me?
 
B

Bob Phillips

Where, you are using a forum.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
J

John

I had a cell with text in my range... sorry for the confusion and thanks for
all the help
 
Top