nesting issue in functions

[Guest]

How can I get around the 7 nesting limitations. This is my formula
=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=5000),19.99)))))
This works but I would like to have multiple values for A10. I tried to copy
it and change the value of a10 each time but I can only add 2 more.
Example:
=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=5000),19.99)))))=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="Pumpco.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="Pumpco.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="Pumpco.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="Pumpco.",$G$27>=5000),19.99)))))
This returns a value of false.
Any help would be appreciated
Thanks

 

[Niek Otten]

Look in HELP for the VLOOKUP function.
Additional help can be found here:

http://www.contextures.com/xlFunctions02.html

 

[Bob Phillips]

Mike,

Try this

=IF(ISNUMBER(MATCH($A$10,{"Pumpco","quanta utility services,
LLC."},0)),VLOOKUP($G$27,{0,0.33;500,0.75;1000,0.9;2000,9.99;5000,19.99},2),
"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

How can I get around the 7 nesting limitations. This is my formula
=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=5000),19.99)))))
This works but I would like to have multiple values for A10. I tried to copy
it and change the value of a10 each time but I can only add 2 more.
Example:
=IF(AND($A$10="quanta utility services,
LLC.",$G$27<499),0.33,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=499,$G$27<=999),0.75,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=2000,$G$27<=4999),9.99,IF(AND($A$10="quanta utility services,
LLC.",$G$27>=5000),19.99)))))=IF(AND($A$10="quanta utility services,

LLC.",$G$27<499),0.33,IF(AND($A$10="Pumpco.",$G$27>=499,$G$27<=999),0.75,IF(
AND($A$10="Pumpco.",$G$27>=1000,$G$27<=1999),0.99,IF(AND($A$10="Pumpco.",$G$