formula too long error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm getting an error "formula too long" for the folloiwng IF statement:

=IF(B15="",0,IF(B19="Full Splashâ€, INDEX(‘Sheet 1’!$A$1:$L$3, MATCH
($B$5,’Sheet 1’!$A$1:$A$3,), MATCH($A$13,’Sheet
1’!$C$1:$L$1,))*VLOOKUP(B15,’Data Table’!$U$10:$V$15,2,FALSE), (INDEX(‘Sheet
1’!$A$6:$L$8, MATCH($B$5,’
Sheet 1’!$A$6:$A$8,),MATCH($A$13,’Sheet 1’!$C$6:$L$6,))*VLOOKUP(B15, ’Data
Table’!$U$10:$V$15,2,FALSE)))

I know this doesn't exceed the Nested If limit so I don't know what the
problem is.

Can I not use INDEX/MATCH/VLOOKUP within my IF statement?
 
I believe Excel formulae are limited to 255 characters and yours is > 300. It
has nothing to do with the use of INDEX, MATCH or LOOKUP.
 
Also could be my imagination but after counting brackets a couple times I
counted 11 left brackets and only 10 right brackets in the formula. For
every left there must be a right.
 
Depending upon version of Excel, formula length can be 1024 characters.

2002 and newer allow the 1024 chars.

See help on specifications and limits.


Gord Dibben MS Excel MVP
 
I have Excel 2002, so why the "formula is too long" error? I guess it's an
erroneous error message.
 
First, I count only 10 right parentheses and 11 left. Second, I'm finding
illegal characters:
1. The *double quote* following the text "Full Splash" isn't actually a
double quote but char 148 which looks almost identical.
2. The *single quotes* surrounding the sheet names (Sheet1 and Data Table)
are in fact char 146 instead which looks almost identical.

The above mentioned characters may look the same but are not accepted
worksheet formula syntax. Strange? Where did the formula come from?

Regards,
Greg
 
Back
Top