return value if 3 conditions met

[bumstead]

Help I am stumped! I have been trying to write a formula for the following.

I have a range ("Annual") that has 8 possible outcomes. I need to get to the correct item in Annual, depending on whether 3 conditions are met.

User enters 3 amounts (loan term, loan amount, LTV). The user entries are compared to 3 criteria. There are 8 possible outcomes, depending on whether:
- loan term is <= or > 15 years
- loan amount is <= or > $625,500
- LTV <= or > 95% (if loan amount is > 625,500), OR 90% (if loan amount is <= 625,500)

Formula result is returned in cell $G$13. I've uploaded a sample file.

I tried INDEX MATCH but it's not really a lookup table, and nested IFs are a nightmare.

Any help whatsoever would be utterly utterly appreciated.

 

[eibi]

Welcome to the forums!

See attached; I've tried to create what you're asking for...but this is pretty preliminary. Is it doing what you want?

 

[Chihiro]

Alternate method.

 

[bumstead]

Thank you so much, EiBi and Chihiro! It works great, except for one thing.

Cells $E$5 to $E$8 need to be <= or > .90, as follows:
E5 <=.90
E6 >.90
E7 <=.90
E8 >.90

When I changed it, the formula got messed up.
Can't tell you how much I appreciate this!!

 

[bumstead]

My apologies -- I had a typo in those cells in the original spreadsheet.

 

[bumstead]

Sorry to be unclear -- I've attached revised to show you what I mean about cells E5 to E8.

 

[Chihiro]

For E8, did you mean >.90?

See attached.

 

[eibi]

That's a curveball!

I've modified mine accordingly.

See attached.

All best!

 

[bumstead]

Yes I did. HUMONGOUS thanks!!!!!

 

[bumstead]

Thank you Eibi!!!

 

[bumstead]

Ugh, as it turns out there is an additional factor that was originally overlooked
For loan amounts > 625K, the LTV can either be <=78, >78 and <=90, or >90, not just greater than/less than 90.

I tried to update the formula in the attached as follows:
=IF(F4>15, IF(F10>0.95,">.95","<=.95"), IF(F7<=625500, IF(F10>0.9,">.90",”<=.90”), IF(F10>0.9,">.90", IF(AND(F10>0.78, F10<=0.9),"<=.90","<=.78") ) ) )

It's quite convoluted -- I can't figure out why it's not working. Is there a better way to do this??

Thanks in advance if you have time to look at this.

 

[Chihiro]

This should do it.
=IF(G4>15,IF(G10>0.95,">.95","<=.95"),IF(G10>0.9,">.90",IF(AND(G7>625500,G10<=0.9,G10>0.78),">.78 and <=.90",IF(AND(G7>625500,G10<=0.78),"<=.78","<=.90"))))

 

[eibi]

My original path -- with the SUMPRODUCT formula -- works well for matching values or binary options, but it becomes much more cumbersome with ranges like >78 and <=90.

Now that we've got new criteria, I don't think my solution is going to be the best fit.

The direction Chihiro's has gone will work better, I think.

 

[bumstead]

Thanks Eibi, and thanks again for coming to the rescue, Chihiro! I am amazed by the awesomeness on this forum.