Testing for error : was 2nd Occurence

  • Thread starter Thread starter Rodney
  • Start date Start date
R

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
 
The error part comes from the SMALL function so you need to test there

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


Regards,


Peo Sjoblom
 
=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
 
Not necessary to drag the whole formula into the error check, the num error
comes only from the SMALL part
 
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.........
Click to expand...
Thats a wonderful idea.........and you might throw in a hug for good
measure.......

Vaya con Dios,
Chuck, CABGx3
 
Back
Top