Testing for error : was 2nd Occurence
 

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

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

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


Regards,


Peo Sjoblom

"Rodney" wrote:


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

=IF(ISERR(INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$629 33=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


"Rodney" wrote in message
...

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

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

--
Regards,

Peo Sjoblom


"CLR" wrote in message
...
=IF(ISERR(INDEX($E$2:$E$62933,SMALL(IF($B$2:$B$629 33=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


"Rodney" wrote in message
...

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

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.........

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



"Rodney" wrote in message
...
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.........