hobgoblinvasya
Board Regular
- Joined
- Jun 29, 2005
- Messages
- 215
hi all
as the title says i have the following problem:
the formula:
Now if i add the following error check to it at the beginning of the formula (i simply put 0 both times to just test the formula)....
i get an error. actually im even unable to put this formula, excel refuses to accept it as a valid one, highlighting one of the trim functions. while the first one work just fine. all i added is - "if(iserror(" i the beginning and "),0,0)" at the end.
does anyone knows what causes this error?
p.s. just to make it a bit clearer the formula first gets a market
then looks for values depending on the market, hence the 2 indexes.
as the title says i have the following problem:
the formula:
Code:
=IF(B40=0,"",IF(ISERROR(INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))),INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))&SI(HJ40="Secteur C-ville MTL"," CV","")))
Now if i add the following error check to it at the beginning of the formula (i simply put 0 both times to just test the formula)....
Code:
=if(iserror(IF(B40=0,"",IF(ISERROR(INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))),INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),INDEX(F1:F30,MATCH(INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3),G1:G30,0))&SI(HJ40="Secteur C-ville MTL"," CV","")))),0,0)
i get an error. actually im even unable to put this formula, excel refuses to accept it as a valid one, highlighting one of the trim functions. while the first one work just fine. all i added is - "if(iserror(" i the beginning and "),0,0)" at the end.
does anyone knows what causes this error?
p.s. just to make it a bit clearer the formula first gets a market
Code:
INDEX(N1:Y10000,MATCH(TRIM(B40),Z1:Z10000,0),3)
then looks for values depending on the market, hence the 2 indexes.