Multiple Criteria If Condition - Identify highest number based upon preceding criteria

Hi All,

I am trying to build a bit of a tracker within Excel/Google Sheets to identify which 'home loan' has the combination of highest interest rate PLUS loan/mortgage size.

The formula I've built so far works for the below conditions;

1. There is only 1 loan with the highest interest rate, or;
2. If there are multiple loans that have the highest interest rate, the loan that is the largest is included within that condition.

Where this formula doesn't work is when;

• When there are multiple loans with the highest interest rate, however these loans are NOT the largest loan size... I believe the issue is due to the fact I'm including a 'max' statement as part of the match condition. Please see cell reference I8:L10 as an example.

I'm unsure how to achieve this within a formula to identify;

1. The loan with the highest interest rate
2. Of the loans with the highest interest rate, which has the largest mortgage/loan size.

Please note the Interest Rate's vary within the data set

Formula Used: =if(countif($E3:$H3,max($E3:$H3))>1, if(iserror(index($E$1:$H$1,match(1,(max($E3:$H3)=E3)*(max($A3:$D3)=A3),0))=I$1),"Inactive","Active"), if(index($E$1:$H$1,match(max($E3:$H3),$E3:$H3,0))=I$1,"Active","Inactive"))


Thanks in advance for any assistance!