Hi,

The limit in Excel 2003 and earlier the limit is 7 levels although there are
a number of ways to beat that. In 2007 that limit is 64!

Even so the correct approach is VLOOKUP(Key,Table,Column,Type)

You would set up a Table like this in C1:D3:

1000 5%
2000 7%
3000 11%

If you want the rate for $1540 in cell A1 the formula would be
=VLOOKUP(A1,C1:D3,2,TRUE)
2 means you want the value back from column 2 of the table. TRUE means you
are doing an approximate match, that is if the value 1540 isn't found in the
first column then the one above it (a lower row number) is used. In our
example 5% is returned. when you are using an approximate match the table is
always sorted ascending on the first column, this is how Excel knows which
one to pick.

--
Thanks,
Shane Devenshire


"Bitter Clinger" wrote:

It looks as if VLOOKUP may work but I have other questions now. In using an
array, do I have to use constants, or can I use formulas? I need to take
the sales figure and find the range it is in and then multiply it by a set
percentage to come up with the comission. As the sales figure goes higher,
so does the comission percentage. So how would I use VLOOKUP to find the
range the sales fits into, and then calculate the correct comission and
place it in the proper cell? And can the array reside on another sheet in
the workbook, and if so, how would you reference the other sheet?

Sorry for so many questions.

"Don Guillett" wrote in message
...
Look in the help index for LOOKUP or VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bitter Clinger" wrote in message
...
I'm trying to write a formula to check for sales totals and then
calculate the correct comission. I need to test for nine conditions but
Excel limits the nesting to seven. How do I get around this limitation?