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