Fetch highest number

[am_gup123]

Hi,

I need help in writing an excel formulae to achieve this –

In Sheet1, there are 4 columns Date,A,B,C,D. These columns contain different numbers while the date field contains date, the data is sorted in ascending order of date.
In Sheet2, I have a date range (say date1 & date2).

What I want is to fetch the highest number among columns A,B,C,D in sheet1 in the specified date range (date 1 & date2).

Example-
Sheet1 :
Date------A-B-C-D
1-Jan-17-1-2-3-5
2-Jan-17-6-4-5-7
3-Jan-17-1-2-5-3
4-Jan-17-3-4-3-6
5-Jan-17-6-4-1-7
6-Jan-17-2-7-9-4
7-Jan-17-6-1-2-4
8-Jan-17-3-6-9-3
9-Jan-17-1-3-2-1

Sheet2:
Date1=3-jan17 ; date2=7-jan-17
Highest number between this date range is 9…..so 9 should be displayed.

Thanks.

 

[Scott T]

If you do not have the maxifs function then this should work
where Sheet1!B1:E9 is your numbers, Sheet1!A1:A9 is the dates and A1 and b1 have your dates in Sheet2

=MAX(INDEX(Sheet1!B1:E9,MATCH(A1,Sheet1!A1:A9,0),0):INDEX(Sheet1!B1:E9,MATCH(B1,Sheet1!A1:A9,0),0))

 

[pgc01]

Hi

With the table in A:E, the start date in G2 and the end date in G3, try:

=MAX(IF(A2:A10>=G2,IF(A2:A10<=G3,B2:E10)))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[am_gup123]

Excellent !!...that worked.....

Thanks a lot for your help.

 

[am_gup123]

That worked.....

Thanks a lot for your help.

 

[Nishant94]

Excel 2016
	A	B	C	D	E
1	Date	A	B	C	D
2	01-Jan-17	1	2	3	5
3	02-Jan-17	6	4	5	7
4	03-Jan-17	1	2	5	3
5	04-Jan-17	3	4	3	6
6	05-Jan-17	6	4	1	7
7	06-Jan-17	2	7	9	4
8	07-Jan-17	6	1	2	4
9	08-Jan-17	3	6	9	3
10	09-Jan-17	1	3	2	1
11
12
13
14	Date 1	Date 2	Option 1	Option 2
15	03/01/17	07/01/17	9	9
Sheet5

Cell Formulas
Range	Formula
D15	=MAX(INDEX((A2:A10>=A15)*(A2:A10<=B15)*B2:E10,0))
C15	{=MAX((A2:A10>=A15)*(A2:A10<=B15)*B2:E10)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[am_gup123]

Thanks a lot @Scott T,@pgc01,@Nishant for your help, it really helped me a lot !!