error in formula using ISERROR

=if(iserror(p59,"need matl",ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R59,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M59),3)))

excel says there's a problem with this and it appears to be with "need matl". "" and 0 don't work either.


BTW, the end of the formula from ROUND on works properly alone

Originally Posted by carstowal

=if(iserror(p59,"need matl",ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R59,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M59),3)))

excel says there's a problem with this and it appears to be with "need matl". "" and 0 don't work either.


BTW, the end of the formula from ROUND on works properly alone

=if(iserror(p59,"need matl"

in above part of your formula there should be some logical operator between p59 and "need matl" (like <>, =, > , < etc) check it.

I tried to shorted my formual by using =p59, sorry

my original formula was
=IF(ISERROR(INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R57,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0))*M57,"need matl",ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R56,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M56),3)))

the two imbedded formulas work fine alone.
they don't work when adding the
IF(ISERROR(formula 1, "need matl" ,formula 2)

Originally Posted by carstowal

I tried to shorted my formual by using =p59, sorry

my original formula was
=IF(ISERROR(INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R57,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0))*M57,"need matl",ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R56,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M56),3)))

the two imbedded formulas work fine alone.
they don't work when adding the
IF(ISERROR(formula 1, "need matl" ,formula 2)

Formula 1 is:
INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R57,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0))*M57

Formula 2 is:
ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R56,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M56)

IF function will return "need matl" if Formula 1 returns an error otherwise it will return the result of Formula 2

it seems that 3 at the end of your formula is an extra argument which do not fit in IF function's syntex.
check it.

Regards.

the 3 belongs to the ROUND function

even if I remove the ROUND & 3 so that formulas 1 & 2 are identical, it still doesn't accept the formula

Originally Posted by carstowal

the 3 belongs to the ROUND function

even if I remove the ROUND & 3 so that formulas 1 & 2 are identical, it still doesn't accept the formula

try this

=IF(ISERROR(INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R57,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0))*M57),"need matl",ROUND(((INDEX('MAT''L est worksheet'!A$2:M$76,MATCH(R56,'MAT''L est worksheet'!A$2:A$76,0),MATCH('MAT''L est worksheet'!M$2,'MAT''L est worksheet'!$2:$2,0)))*M56),3))