Try it like this. It's more efficient and more robust:

=IF(ROWS(E$1:E1)<=COUNTIF(A$1:A$6,D$1),INDEX(B$1:B $6,SMALL(IF(A$1:A$6=D$1,ROW(B$1:B$6)),ROWS(E$1:E1) )-MIN(ROW(B$1:B$6))+1),"")

--
Biff
Microsoft Excel MVP


"muddan madhu" wrote in message
...
try this

in E1 put this formula ( use ctrl + shift + enter ) and drag it

=IF(ISERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D$ 1,ROW($A$1:$A
$6)),ROW(1:1)),0)),"",INDEX($B$1:$B$6,SMALL(IF($A$ 1:$A$6=$D$1,ROW($A
$1:$A$6)),ROW(1:1)),0))



On Sep 26, 1:40 pm, Wallace wrote:
my excel matrix is set up as such:
A1=BOB; A2=TIM; A3=BOB; A4=SUE; A5=JOHN; A6=BOB;
B1=100; B2=101; B3=102; B4=103; B5=104; B6=105;
D1=BOB;
Which function should I use in E1 that will lookup BOB in the matrix above
and,
if first value is found, it will list the second value for BOB in cell E2
and,
if first value is found, it will list the third value for BOB in cell E3.

Thx.