Thread: nested if statement- trying to avoid but don't know how

Okay,

I have another formula issue. I am trying to write a formula to tell if the rate is "Above", "Exact", or "Below" a certain rate. The issue is there are three different variables to use to determine: Amount (has 6 different ranges), rating (could be 1-6), and the rate itself. Below is an attempt for a formula that ended up being a nested if statement which doesn't work. I have also attached the worksheet so you can see what I am working on. Any suggestions on writing this formula? I am not too excel savy, so you might need to explain a little. Thanks so much!

=IF((A4=1)*AND(2501000>=C4)*AND(B4<(MAX('Rate Matrix'!E10:F10))),"Below",(IF((A4=1)*AND(2501000>=C4)*AND(B4=(MAX('Rate Matrix'!E10:'Rate Matrix'!F10))),"Exact",(IF((A4=1)*AND(2501000>=C4)*AND(B4>(MAX('Rate Matrix'!E10:F10))),"Above",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4<(MAX('Rate Matrix'!E9:F9))),"Below",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4=(MAX('Rate Matrix'!E9:F9))),"Exact",(IF((A4=1)*AND(2501000>C4>=100100)*AND(B4>(MAX('Rate Matrix'!E9:F9))),"Above",)))))))))))

Test Pricing Sheet.xlsx

I could use column D but I still need to check the other sheet for the rates to compare it against the greater of the two other rates. The rates I am comparing against will change regularly, so I need a formula to allow me to change them without issues. Thoughts?

Hi there

I think I've got this working. To make my version work using your data structure, I had to introduce column C on the Rate Matrix sheet. This is used to look up the amount of the loan.

The function works using INDEX and MATCH.

It works out which row it is looking at using the MATCH statement. It works out which column using the Risk Rating and multiplying by 3 (as there are 3 columns for each risk rating).

Sample attached.

Regards, Rob.