Vlookup 2nd value

[orzik]

All,
This is my challenge:
I need to lookup not only first value but als 2nd, 3rd etc.
I searched the board for any clues but couldn't find what I need.

In below example I would like to lookup phone numbers that belong to JOHN (E1). Since he is listed 3 times 3 numbers will be returned. I can spice it up just need the hard work idea to start.
Thanks,

Book1.xls
	A	B	C	D	E	F
1	Name	Phone		LookupName	John
2	John	222		1	222	vlookup1stvalue
3	Ana	333		2	212	vlookup2ndvalue
4	Tom	444		3	213	vlookup3rdvalue
5	John	212		4		vlookup4thvalue
6	John	213		5		vlookup5thvalue
7	Ana	313
8	Ana	555
Sheet1

 

[meytithveasna]

IFERROR(INDEX($A$1:$A$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2),"")) CtrlShift,Enter

 

[Aladin Akyurek]

IFERROR(INDEX($A$1:$A$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2),"")) CtrlShift,Enter

That's not robust... Try rather:

In F1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$A$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)-ROW($A$1)+1),ROWS($F$1:F1))),"")