MAX SUM based on criteria (SUMPRODUCT array)

[itfmm]

Hello,

I'm trying to generate a lookup that would return a value in a column based on several criteria and the SUM MAX of two figures. Been using INDEX(SUMPRODUCT()) for all of my lookups up till now, so thinking it may be just a matter of appropriately nesting MAX, SUM, SUMPRODUCT, and INDEX but keep getting stuck on the logic.

Basically what I need:

Retrieve the value in Column E based on MAX SUM of columns G and L where A:A=O2,D:D=O4,F:F and K:K =P2, H:H=Q2

[/IMG]

Thanks in advance!

 

[jorismoerings]

Hi,

don't know if i understand you correctly but is this what your after:

Book1
	A	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	program name	Note rate	Coupon	Issue month	Adjuster	Security type		Coupon	Issue month	Security price
2	Conv30	2750	2	08/01/2016	-1,6	FNMA 30		2	08/01/2016	99156			conv30	09/01/2016	FNMA 30
3	Conv30	2875	2	08/01/2016	-1,23	FNMA 30		2	08/01/2016	100969			Rate	Price
4	Conv30	3000	3	08/01/2016	-0,81	FNMA 30		3	08/01/2016	103578			2750	-124206,42
5	Conv30	3125	3	08/01/2016	-0,46	FNMA 30		3	08/01/2016	105313			2875	0
6	Conv30	2750	2,5	09/01/2016	-1,47	FNMA 30		2,5	09/01/2016	107078			3000	-83415,2
7	Conv30	2750	2,5	09/01/2016	-1,14	FNMA 30		2,5	09/01/2016	108953			3125	-52390
8	Conv30	3000	3,5	09/01/2016	-0,8	FNMA 30		3,5	09/01/2016	104269
9	Conv30	3125	3,5	09/01/2016	-0,52	FNMA 30		3,5	09/01/2016	100750
Sheet1

Cell Formulas
Range	Formula
P4	{=MAX(IF(($A$2:$A$9=$O$2)*($D$2:$D$9=O4)*($F$2:$F$9=$P$2)*($K$2:$K$9 =$P$2)*($H$2:$H$9=$Q$2),($G$2:$G$9)*($L$2:$L$9)))}
Press CTRL+SHIFT+ENTER to enter array formulas.