#Num error
 

Is there any way to get #NUM! error to be hidden? The formula works great but
for every instant returned that does not have a value iit shows this error.

Formula used:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))
then ctl shift enter

Names in Column A
Companies in Column B
Name to look up C1

#Num error
 

You do it this way:
=if(iserror(yourformula),0,yourformula)

In Excel 2007, you can use the IFError function.

Regards,
Fred.

"dadof4girls" wrote in message
...
Is there any way to get #NUM! error to be hidden? The formula works great
but
for every instant returned that does not have a value iit shows this
error.

Formula used:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))
then ctl shift enter

Names in Column A
Companies in Column B
Name to look up C1

#Num error
 

In XL03
=IF(ISERROR(your formula),"",your formula)

=IF(ISERROR(INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$ C$1,
ROW($A$1:$A$30)),ROW(A1)))),"",
INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$1 :$A$30)),ROW(A1))))


In XL07
=IFERROR(INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1 ,
ROW($A$1:$A$30)),ROW(A1))),"")


If this post helps click Yes
---------------
Jacob Skaria


"dadof4girls" wrote:

Is there any way to get #NUM! error to be hidden? The formula works great but
for every instant returned that does not have a value iit shows this error.

Formula used:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))
then ctl shift enter

Names in Column A
Companies in Column B
Name to look up C1

#Num error
 

Let's assume the first cell you enter the formula in is cell E1.

Array entered** :

=IF(ROWS(E$1:E1)COUNTIF($A$1:$A$30,$C$1),"",INDEX ($B:$B,SMALL(IF($A$1:$A$30=$C$1,ROW($A$1:$A$30)),R OWS(E$1:E1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks.


--
Biff
Microsoft Excel MVP


"dadof4girls" wrote in message
...
Is there any way to get #NUM! error to be hidden? The formula works great
but
for every instant returned that does not have a value iit shows this
error.

Formula used:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))
then ctl shift enter

Names in Column A
Companies in Column B
Name to look up C1