I am at a loss! I'm trying to determine a commission rate based off of four criteria:

1) Renewal Written Premium
2) New Life Commission
3) Retained Policy Count
4) Persistency Rate

Each of those categories has a range for the possible data that could be entered from (i.e., Persistency Rate could be a SINGLE number between 0 - 69.99, or 70 - 74.99) and by falling into one of those ranges, a different commission rate would be possible. I've included a picture of what the table / range(s) looks like:

\1

The commission rate is based off of the LOWEST factor from the four categories.

So, let me show you a possible example:

\1

In this instance, since the lowest factor is 16 (in the Retained Life Policy Count column), then the commission rate would be 1.5%.

Now, what I'm trying to do is create a formula that will check those ranges and produce the commission rate. I created those pictures merely for examples, not actually what I'll be using (however I'm sure I could find a way to adapt a formula to work if one is created from those pictures).

I've examined the idea of nested IF statements, but A) I don't think I can nest enough of them for it to work, and B) it would be ridiculously dirty looking and cumbersome. I'm not super familiar with VLOOKUP, but my guess is that's what I have to use to determine the lowest factor in the various ranges.

Any help you guys / gals can provide would be greatly appreciated! Even just a point(er) in the right direction will give me a leg to stand on. Thanks in advance for any help you can provide!

~Michael