Testing for error : was 2nd Occurence

[Rodney]

I have my statement working OK, thanks to all.
=INDEX($E$1:$E$62933,SMALL(IF($B$1:$B$62933=B62938,ROW($E$1:$E$62933)),$F$2))

When the name is not found in the array,
I have a #NUM error value.
Is there a way to test for error, and replace with a zero
to the above statement please?


As an aside, I had the devils own trouble getting sense from
the above statement, when I used the array E2:E62933
(I had a header row, and felt I not need to include in the array)
when I used E1:E62933 everything worked fine.
Why is that?

Thankyou.
Rodney

 

[Peo Sjoblom]

The error part comes from the SMALL function so you need to test there

=IF(ISERROR(Small(----)),0,your full formula)


Regards,


Peo Sjoblom

 

[CLR]

=IF(ISERR(INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$62933=B62938,ROW($E$2:$E$6293
3)),$F$2))),0,INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$62933=B62938,ROW($E$2:$E$
62933)),$F$2)))

All on one line, watch out for wordwrap......

Vaya con Dios,
Chuck, CABGx3

 

[Peo Sjoblom]

Not necessary to drag the whole formula into the error check, the num error
comes only from the SMALL part

 

[Rodney]

Thank you gentlemen, both.
There is a palpable joy when finally,
something that seemed utterly obtuse, works.

I think I shall purchase for my wife, some flowers.........

 

[CLR]

You're welcome Rodney...........

I think I shall purchase for my wife, some flowers.........

Thats a wonderful idea.........and you might throw in a hug for good
measure.......

Vaya con Dios,
Chuck, CABGx3