As Bob suggested, use VLOOKUP. He provided an example that looks like it will
do what you are attempting.

For more, see:
http://www.contextures.com/xlFunctions02.html

tj

"Carl Hilton" wrote:

OK, I see there is a 7 nested limit... How can I get around this for this
formula??

THANKS


"Carl Hilton" wrote in message
...
The following works:


=IF(AND(B22=150,B22<=159),18.5,IF(AND(B22=160,B2 2<=169),15.9,IF(AND(B22=1

70,B22<=179),13.8,IF(AND(B22=180,B22<=189),12.5,I F(AND(B22=190,B22<=199),1
0.7,IF(AND(B22=200,B22<=209),9.6,IF(AND(B22=210, B22<=219),9.2)))))))/2

However, the below does NOT


=IF(ISNUMBER(B2),IF(AND(B22=150,B22<=159),18.5,IF (AND(B22=160,B22<=169),15

..9,IF(AND(B22=170,B22<=179),13.8,IF(AND(B22=180 ,B22<=189),12.5,IF(AND(B22

=190,B22<=199),10.7,IF(AND(B22=200,B22<=209),9.6, IF(AND(B22=210,B22<=219),
9.2)))))))/2,"")

By adding the ISNUMBER and first IF, casuses the formula to not work... Is
it the length of the formula?

Carl