Thread: Nested If statement

Good afternoon,

I am working on setting up a worksheet that needs to review three different variables. I need the space to say either "yes" to an exception or "no". It needs to review the three variables (amount, rate, and rating) to see where it falls into the table. I am not too excel savy so the only solution I have come up with is a nested if statement. The problem is it is too messy and doesn't always work. I am not sure what to do to get it to work. Attached is the spreadsheet showing the problem but here is the formula as well.

=IF((C10>=2501000)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E10:'Pricing Matrix'!F10))),"yes",(IF((C10>=2501000)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H10:'Pricing Matrix'!I10))),"yes",(IF((C10>=2501000)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K10:'Pricing Matrix'!L10))),"yes",(IF((C10>=2501000)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N10:'Pricing Matrix'!O10))),"yes",(IF((C10>=2501000)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q10:'Pricing Matrix'!R10))),"yes",(IF((C10>=2501000)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T10:'Pricing Matrix'!U10))),"yes",(IF((2501000>C10>=1001000)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E9:'Pricing Matrix'!F9))),"yes",(IF((2501000>C10>=1001000)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H9:'Pricing Matrix'!I9))),"yes",(IF((2501000>C10>=1001000)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K9:'Pricing Matrix'!L9))),"yes",(IF((2501000>C10>=1001000)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N9:'Pricing Matrix'!O9))),"yes",(IF((2501000>C10>=1001000)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q9:'Pricing Matrix'!R9))),"yes",(IF((2501000>C10>=1001000)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T9:'Pricing Matrix'!U9))),"yes",(IF((1001000>C10>=501000)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E8:'Pricing Matrix'!F8))),"yes",(IF((1001000>C10>=501000)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H8:'Pricing Matrix'!I8))),"yes",(IF((1001000>C10>=501000)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K8:'Pricing Matrix'!L8))),"yes",(IF((1001000>C10>=501000)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N8:'Pricing Matrix'!O8))),"yes",(IF((1001000>C10>=501000)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q8:'Pricing Matrix'!R8))),"yes",(IF((1001000>C10>=501000)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T8:'Pricing Matrix'!U8))),"yes",(IF((501000>C10>=151000)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E7:'Pricing Matrix'!F7))),"yes",(IF((501000>C10>=151000)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H7:'Pricing Matrix'!I7))),"yes",(IF((501000>C10>=151000)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K7:'Pricing Matrix'!L7))),"yes",(IF((501000>C10>=151000)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N7:'Pricing Matrix'!O7))),"yes",(IF((501000>C10>=151000)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q7:'Pricing Matrix'!R7))),"yes",(IF((501000>C10>=151000)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T7:'Pricing Matrix'!U7))),"yes",(IF((151000>C10>=51000)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E6:'Pricing Matrix'!F6))),"yes",(IF((151000>C10>=51000)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H6:'Pricing Matrix'!I6))),"yes",(IF((151000>C10>=51000)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K6:'Pricing Matrix'!L6))),"yes",(IF((151000>C10>=51000)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N6:'Pricing Matrix'!O6))),"yes",(IF((151000>C10>=51000)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q6:'Pricing Matrix'!R6))),"yes",(IF((151000>C10>=51000)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T6:'Pricing Matrix'!U6))),"yes",(IF((51000>C10>0)*AND(C12=1)*AND(C8<(MAX('Pricing Matrix'!E5:'Pricing Matrix'!F5))),"yes",(IF((51000>C10>0)*AND(C12=2)*AND(C8<(MAX('Pricing Matrix'!H5:'Pricing Matrix'!I5))),"yes",(IF((51000>C10>0)*AND(C12=3)*AND(C8<(MAX('Pricing Matrix'!K5:'Pricing Matrix'!L5))),"yes",(IF((51000>C10>0)*AND(C12=4)*AND(C8<(MAX('Pricing Matrix'!N5:'Pricing Matrix'!O5))),"yes",(IF((51000>C10>0)*AND(C12=5)*AND(C8<(MAX('Pricing Matrix'!Q5:'Pricing Matrix'!R5))),"yes",(IF((51000>C10>0)*AND(C12=6)*AND(C8<(MAX('Pricing Matrix'!T5:'Pricing Matrix'!U5))),"yes","no")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Is there an easier way to get this to work? And if there is, I might need a little more detail explaining to help understand. I don't know if the "and" statements in there work as well. Lender Pricing Worksheet.xlsx

Dear Dumpster,

YOu can try using a VLOOKUP FUNCTION WITH the TRUE APPROACH WHERE the Range Lookup will be carried out..

Just Make 2 Column Matrix where you put the Ranges in the First Column and the Values you want in the second column and then use VLOOKUP formula with the 1 or TRUE parameter.

Example:

MAtrix

Col A--Col B
0 Red
100 Blue
200 Orange

So if I put this into a VLookup for a lookup value of 80 the answer should be Red..

=vlookup(80,A:B,2,TRUE)

rEGARDS
e4excel

Pl Try this function.

=IF(AND(C12>0,C12<=6),IF(OR(AND(51000>C10>0,C8<(MAX('Pricing Matrix'!E5:'Pricing Matrix'!F5))),AND(501000>C10>=151000,C8<(MAX('Pricing Matrix'!E7:'Pricing Matrix'!F7))),AND(1001000>C10>=501000,C8<(MAX('Pricing Matrix'!E8:'Pricing Matrix'!F8))),AND(2501000>C10>=1001000,C8<(MAX('Pricing Matrix'!E9:'Pricing Matrix'!F9))),AND(C10>=2501000,C8<(MAX('Pricing Matrix'!E10:'Pricing Matrix'!F10)))),"yes","NO"))

I tried this one and it didn't work. It matters if C12 is 1, 2, 3, 4, 5, or 6 which causes additional formulas needed. Is there a way to include that in here?