Combining an if statement, vlookup, and max possibly?

[SHARRIS2013]

Hi, i am trying to find a solution to the below but am stumped! Can anyone suggest a solution?

Tab 1
In column A i have a range of dates say Jan 1st to Feb 28th
In column B i have a range of values relating to each date

Tab 2
I have a range of data in row 1, two cells B1 and C1 contain dates Jan 15th and Feb 2nd respectively
In column E1 i want a formula to look at the dates in B1 and C1 and then use the table in Tab1 to look up the values between the corresponding dates and return the max value during those dates, so if Jan 24th is the highest value of 200 i would get a value of 200 in cell E1


One step further would be that i have a figure in D1, so in E1 if the formula could be enhanced to say whether D1 is greater than the max value mentioned above that would be great. Alternatively i will use the max figure above and then a seperate If statement?

I hope the above makes sense

Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel. Example:

=MAX(INDEX((Sheet1!A1:A59>=B1)*(Sheet1!A1:A59<=C1)*Sheet1!B1:B59,))

 

[Trevor G]

Welcome to the forum,

Check this YouTube video out it will show you. Also example below: Headings must be the same.



Excel Magic Trick 935: MAX Value Between Two Dates: DMAX, MAX-IF-IF, or AGGREGATE Function? - YouTube

Excel Workbook
	A	B	C	D	E	F
1	Dates	Values		Date1	Date2
2	01/01/2013	23		03/01/2013	06/01/2013
3	02/01/2013	24
4	03/01/2013	25		Dates	Dates	Values
5	04/01/2013	26		>=3/1/13		42
6	05/01/2013	27
7	06/01/2013	28
8	07/01/2013	29
9	01/01/2013	30
10	02/01/2013	31
11	03/01/2013	32
12	04/01/2013	33
13	05/01/2013	34
14	06/01/2013	35
15	07/01/2013	36
16	01/01/2013	37
17	02/01/2013	38
18	03/01/2013	39
19	04/01/2013	40
20	05/01/2013	41
21	06/01/2013	42
22	07/01/2013	43
Sheet2

 

[SHARRIS2013]

Thanks for the quick responses! I will test out the first suggestion tomorrow morning and take a look at the YouTube video too! Really appreciate your time to help me!