[SOLVED] Nested IF Statement Issue

Hello,

I'm taking over a sales document from a co-worker that we us to calculate sales bonuses and payout based on target ranges.

There are 5 categories of payout:
EXCEPTIONAL
EXCEEDS TARGET
MEETS TARGET
BELOW TARGET
UNSATISFACTORY

The current formula is a nested IF formula and it's giving me a "FALSE" result and when I evaluate the formula it throws the "FALSE" in the first condition. The formula is:

=IF(B2<J2,"UNSATISFACTORY",IF(AND(B2>=I2,B2<=J2),"BELOW TARGET",IF(AND(B2>=G2,B2<=H2),"MEETS TARGET",IF(AND(B2>=E2,B2<=F2),"EXCEEDS TARGET",IF(B2>=D2,"EXCEPTIONAL")))))

The values are:
B2=5.75
J2=2
I2=2
G2=3
H2=3
E2=4
F2=5
D2=6

I also attached a spreadsheet with the formula and the values. I'm not sure if it's the logic because two of the ranges are the same values or if it's written in the wrong order or what.

What am I missing? Can someone give me a tip?

Thank you in advance!

With VLookup.

See the attached file.

Try this

Formula:

Please Login or Register  to view this content.

The formula you inherited was needlessly complicated. Unless the values you have in columns F, H and J are used somewhere else, you can get rid of them.

Thank you Craig! This worked perfectly and I understand the thought process as well so it was very helpful.