As far as the length is concerned, assign names to the ranges containing the
table(s) (i.e. use Table as the name of the range 'Product Line
Lookup'!A$29:F$69) and use VLOOKUP instead of LOOKUP. The latter requires two
ranges, the former just a column number, i.e. VLOOKUP(E15,Table,3)

Table would refer to A2:E62 on sheet Product Line Lookup. The above formula
gets data from column C. When you want D, change the 3 to 4, etc.

=IF(D15="CPS",IF(M15<50000,M15*VLOOKUP(E15,Table,3 ),IF(AND(M1550000,M15<200000),
50000*VLOOKUP(E15,Table,3)+(Orders!M15-50000)*VLOOKUP(Orders!E15,Table,4),
IF(AND(M15200000,Orders!M15<500000),50000*VLOOKUP (E15,Table,3)+150000*VLOOKUP(Orders!E15,Table,4)
+(Orders!M15-200000)*VLOOKUP(Orders!E15,Table,5),IF(M15500000, 50000*VLOOKUP(E15,Table,3)
+150000*VLOOKUP(Orders!E15,Table,4)+200000*VLOOKUP (Orders!E15,Table,5)
+(Orders!M15-500000)*VLOOKUP(Orders!E15,Table,6))))),N15*M15)

(I may have the parentheses messed up in the above...)

If you have a problem with nested IF statements, you might get some help by
creating another lookup table that determines the multiplier, i.e. instead of
IF(AND(M1550000,M15<200000),50000* .... to something like
VLOOKUP(M15,NewTable)*VLOOKUP(E15... which would return the number 50000.

But looking at your data, I wonder if what you need is a table that is
structured like our income tax tables, with the income amount in column 1, a
base dollar amount in column 2, and a multipler in column 3.

On Fri, 09 Jul 2004 10:09:31 -0700, Michael McClellan
wrote:

I have made a formula in excel that returns an error "Formula too long".
What are the limitations of excel? Is there a way around this? Here is
the formula.

=IF(D15="CPS",IF(M15<50000,M15*LOOKUP(E15,'Produc t Line
Lookup'!A$29:A$62,'Product Line
Lookup'!C$29:C$62),IF(AND(M1550000,M15<200000),5 0000*LOOKUP(E15,'Produc
t Line Lookup'!A$29:A$62,'Product Line
Lookup'!C$29:C$62)+(Orders!M15-50000)*LOOKUP(Orders!E15,'Product Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$D$29:$D$62),IF(AND(M15200000,Orders!M15 <500000),50000*LOOKUP(E
15,'Product Line Lookup'!$A$29:$A$62,'Product Line
Lookup'!$C$29:$C$62)+150000*LOOKUP(Orders!E15,'Pr oduct Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$D$29:$D$62)+(Orders!M15-200000)*LOOKUP(Orders!E15,'Product Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$E$29:$E$62),IF(M15500000,50000*LOOKUP(E 15,'Product Line
Lookup'!$A29:A62,'Product Line
Lookup'!C29:C62)+150000*LOOKUP(Orders!E15,'Produc t Line
Lookup'!A29:A62,'Product Line
Lookup'!D29:D62)+200000*LOOKUP(Orders!E15,'Produc t Line
Lookup'!A29:A62,'Product Line
Lookup'!E29:E62)+(Orders!M15-500000)*LOOKUP(Orders!E15,'Product Line
Lookup'!A29:A62,'Product Line Lookup'!F29:F62))))),N15*M15)