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.

"dread" wrote:

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?

Do you know of a different way to do this so I don't go over the character
limit?
Thxs

"Toppers" wrote:

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.

"dread" wrote:

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?

Here is a method by using defined names

http://www.cpearson.com/excel/nested.htm

the example shows how to overcome the nesting limits but you can do the same
for long formulas as well by naming parts of them

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"dread" wrote in message
...
Do you know of a different way to do this so I don't go over the character
limit?
Thxs

"Toppers" wrote:

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.

"dread" wrote:

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?

Thank you!

"Peo Sjoblom" wrote:

Here is a method by using defined names

http://www.cpearson.com/excel/nested.htm

the example shows how to overcome the nesting limits but you can do the same
for long formulas as well by naming parts of them

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"dread" wrote in message
...
Do you know of a different way to do this so I don't go over the character
limit?
Thxs

"Toppers" wrote:

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.

"dread" wrote:

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?

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

On Wed, 28 Jun 2006 12:35:01 -0700, Toppers
wrote:

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.

"dread" wrote:

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 have Excel 2002, so why the "formula is too long" error? I guess it's an
erroneous error message.

"Gord Dibben" wrote:

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

On Wed, 28 Jun 2006 12:35:01 -0700, Toppers
wrote:

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.

"dread" wrote:

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?

1024 characters when measured in R1C1 reference style.

(for xl97+)

Toppers wrote:

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.

"dread" wrote:

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?


--

Dave Peterson

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.

"dread" wrote:

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?

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

"dread" wrote:

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?