Find Max value

[Miguelluis]

Hi,
I'm trying to find a formula that works with older versions of excel (not MAXIFS) to look up the client name in G3 the current month in H2 and give me the highest value in H3 for john in January 2019. I can make this work with a MAXIFS [=MAXIFS('12MonthInstitutionalTrading'!H:H,'12MonthInstitutionalTrading'!C:C,TradingTable!A3,'12MonthInstitutionalTrading'!G:G,FrontSheet!$A$3)] but can't find an alternative that will work with older versions of excel!

Can any one help???

	A	B	C	D	E	F	G
1​	Client	TRANSACTION_DATE	TRANS_MONTH	Value		Current Month	January 2019
2​	John	21/05/2019​	May 2019	£150,000,000.00​		John	Max value
3​	John	24/05/2019​	May 2019	£30,000,000.00​		Ben	Max value
4​	John	27/05/2019​	May 2019	£30,000,000.00​
5​	John	27/05/2019​	May 2019	£30,000,000.00​
6​	John	28/05/2019​	May 2019	£30,000,000.00​
7​	John	18/12/2019​	December 2019	£20,000,000.00​
8​	Ben	21/05/2019​	May 2019	£7,067.00​
9​	Ben	09/08/2019​	August 2019	£5,280.00​
10​	Ben	05/09/2019​	September 2019	£376,490.00​
11​	Ben	05/09/2019​	September 2019	£376,490.00​
12​	Ben	05/09/2019​	September 2019	£376,490.00​
13​	Ben	10/09/2019​	September 2019	£87,260.00​
14​	Chris	09/01/2019​	January 2019	£227,391.00​
15​	Chris	11/01/2019​	January 2019	£364,741.00​
16​	Chris	02/01/2019​	January 2019	£685,200.00​
17​	Chris	03/01/2019​	January 2019	£118,508.00​
18​	Chris	04/01/2019​	January 2019	£94,968.00​
19​	Chris	07/01/2019​	January 2019	£111,120.00​
20​	Chris	08/01/2019​	January 2019	£855,249.00​
21​	Chris	10/01/2019​	January 2019	£154,700.00​
22​	John C	15/01/2019​	January 2019	£225,150.00​
23​	John C	14/01/2019​	January 2019	£62,552.00​
24​	John C	16/01/2019​	January 2019	£89,141.00​
25​	John C	08/03/2019​	March 2019	£187,854.00​
26​	Sarah	18/03/2019​	March 2019	£175,082.00​
27​	Sarah	21/03/2019​	March 2019	£259,197.00​
28​	Sarah	21/05/2019​	May 2019	£168,598.00​
29​	Sarah	17/01/2019​	January 2019	£368,363.00​

 

[sandy666]

we even don't know what is your excel version.
maybe update your profile about excel version first (Account details)

and what does that mean: older ? '95, 2000, 2003 or maybe 5.0 for DOS?

 

[steve the fish]

Your provided formula makes no sense with the sample data you have provided.

 

[Peter_SSs]

find an alternative that will work with older versions of excel!

How old? 2010 and later?

20 03 13.xlsm
	A	B	C	D	E	F	G
1	Client	TRANSACTION_DATE	TRANS_MONTH	Value		Current Month	Jan-19
2	John	21/05/2019	May-19	150,000,000.00		John C	225,150.00
3	John	24/05/2019	May-19	30,000,000.00		Chris	855,249.00
4	John	27/05/2019	May-19	30,000,000.00
5	John	27/05/2019	May-19	30,000,000.00
6	John	28/05/2019	May-19	30,000,000.00
7	John	18/12/2019	Dec-19	20,000,000.00
8	Ben	21/05/2019	May-19	7,067.00
9	Ben	9/08/2019	Aug-19	5,280.00
10	Ben	5/09/2019	Sep-19	376,490.00
11	Ben	5/09/2019	Sep-19	376,490.00
12	Ben	5/09/2019	Sep-19	376,490.00
13	Ben	10/09/2019	Sep-19	87,260.00
14	Chris	9/01/2019	Jan-19	227,391.00
15	Chris	11/01/2019	Jan-19	364,741.00
16	Chris	2/01/2019	Jan-19	685,200.00
17	Chris	3/01/2019	Jan-19	118,508.00
18	Chris	4/01/2019	Jan-19	94,968.00
19	Chris	7/01/2019	Jan-19	111,120.00
20	Chris	8/01/2019	Jan-19	855,249.00
21	Chris	10/01/2019	Jan-19	154,700.00
22	John C	15/01/2019	Jan-19	225,150.00
23	John C	14/01/2019	Jan-19	62,552.00
24	John C	16/01/2019	Jan-19	89,141.00
25	John C	8/03/2019	Mar-19	187,854.00
26	Sarah	18/03/2019	Mar-19	175,082.00
27	Sarah	21/03/2019	Mar-19	259,197.00
28	Sarah	21/05/2019	May-19	168,598.00
29	Sarah	17/01/2019	Jan-19	368,363.00
Max

Cell Formulas
Range	Formula
G2:G3	G2	=AGGREGATE(14,6,D$2:D$29/((A$2:A$29=F2)*(C$2:C$29=G$1)),1)

 

[Miguelluis]

Hi, Sorry guys, my version is 2016, but I am sharing this with people that are using 2010!

 

[sandy666]

just use Pivot Table

my version is 2016,

anyway you can update your profile

 

[Miguelluis]

How old? 2010 and later?

Peter, you star! This worked a treat!

THANK YOU!

 

[Peter_SSs]

This worked a treat!

THANK YOU!

You're welcome.