find first occurrence, then 2nd occurrence in another cell

[Hsibbs]

My cell A1 has 117 in it, on another sheet I have 100 records, 8 of which have 117 also with a name in the next cell.
eg on the 2nd sheet I have
117 in D1 and Fred Smith in E1.
117 in D2 and Gloria Jones in E2

I want a formula in cell A2 on my first sheet which finds the FIRST occurence of 117 and gives me back what is in column 2, ie Fred Smith.

I then want in cell A3 to find the SECOND occerence off 117 and give me back Gloria Smith

Any Ideas?

THANK YOU!!!!!!

 

[Robert Mika]

Put 117 in B1 in your Sheet one, then in A2 of the same sheet enter
=IF(ROWS($D$1:$D1)<=COUNTIF('another sheet'!$D$1:$D$100,$B$1),INDEX('another sheet'!$E$1:$E$100,SMALL(IF('another sheet'!$D$1:$D$100=$B$1,ROW($D$1:$D$100)-ROW($A$1)+1),ROWS($D$1:$D1))),"")
(array formula confirm CTRL+SHIFT+ENTER)

 

[Hsibbs]

wow! i will give it a go!
thank you

 

[T. Valko]

My cell A1 has 117 in it, on another sheet I have 100 records, 8 of which have 117 also with a name in the next cell.
eg on the 2nd sheet I have
117 in D1 and Fred Smith in E1.
117 in D2 and Gloria Jones in E2

I want a formula in cell A2 on my first sheet which finds the FIRST occurence of 117 and gives me back what is in column 2, ie Fred Smith.

I then want in cell A3 to find the SECOND occerence off 117 and give me back Gloria Smith

Any Ideas?

THANK YOU!!!!!!

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

Or this...

http://www.mrexcel.com/forum/showpost.php?p=2768110&postcount=4

 

[Hsibbs]

Thank you for a very clear break down and explanation, I needed it!

 

[T. Valko]

Thank you for a very clear break down and explanation, I needed it!

You're welcome. Thanks for the feedback!