iserror and index match

[justme1122]

Hi

So I just added iserror to my formluar and its crazzy big. will be used in 10000+ cells
I also noticed that if I forget to press ctrl+shift+enter I still get a value of 0 when i only really want a 0 if there is no data to get.

Any ideas on how make is smaller?

{=IF(ISERROR(INDEX(INDIRECT(F$1&"!$H:$H"),MATCH(1,(Data!$A2=INDIRECT(F$1&"!$B:$B"))*(Data!$B2=INDIRECT(F$1&"!$A:$A")),0))),0,INDEX(INDIRECT(F$1&"!$H:$H"),MATCH(1,(Data!$A2=INDIRECT(F$1&"!$B:$B"))*(Data!$B2=INDIRECT(F$1&"!$A:$A")),0)))}

 

[Matty]

Hi,

Rather than us trying to guess what your formula is doing, could you share a small example to illustrate the point?

You're much more likely to get a response if you do this.

Matty

 

[T. Valko]

Hi

So I just added iserror to my formluar and its crazzy big. will be used in 10000+ cells
I also noticed that if I forget to press ctrl+shift+enter I still get a value of 0 when i only really want a 0 if there is no data to get.

Any ideas on how make is smaller?

{=IF(ISERROR(INDEX(INDIRECT(F$1&"!$H:$H"),MATCH(1,(Data!$A2=INDIRECT(F$1&"!$B:$B"))*(Data!$B2=INDIRECT(F$1&"!$A:$A")),0))),0,INDEX(INDIRECT(F$1&"!$H:$H"),MATCH(1,(Data!$A2=INDIRECT(F$1&"!$B:$B"))*(Data!$B2=INDIRECT(F$1&"!$A:$A")),0)))}

What version of Excel are you using?

When the formula DOESN'T return an error, what type of data does it return? Does it return TEXT? Numbers? Could return either?