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

1. ## 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!  Register To Reply

2. ## Re: Multiple Criteria If Condition - Identify highest number based upon preceding criteria

What version of Excel are you currently using? Since you joined 5 years ago, I have assumed that you are now using Excel 2010 or later. If not, I can tweak this for Excel 2007.

=IF(J\$1=INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(\$E\$1:\$H\$1)/(\$A3:\$D3=LARGE((\$E3:\$H3=MAX(\$E3:\$H3))*(\$A3:\$D3),1)),1)),"Active","Inactive")

in J3, copied across and down. If this is incorrect, please tell us what the expected answers are (a description of your non-working formula is of very limited use).  Register To Reply

3. ## Re: Multiple Criteria If Condition - Identify highest number based upon preceding criteria

Excel 2007-friendly version:

=IF(J\$1=INDEX(\$1:\$1,,SMALL(IF(\$A3:\$D3=LARGE((\$E3:\$H3=MAX(\$E3:\$H3))*(\$A3:\$D3),1),COLUMN(\$E\$1:\$H\$1)),1)),"Active","Inactive")

This is an array formula.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...  Register To Reply