Formula required for 'LOOKUP'

[delamere]

I would like to find the THIRD occurrence of a value in a sorted list and with a blank if there is none.
VLOOKUP finds the FIRST occurrence.
The sample sheet shows a VLOOLUP attempt with BARNES as the answer; I want PUTNEY to be the result.
Any ideas?

Book2
	A	B	C	D
1	KEY	NAME	ADDRESS	Formula
2	AA	AA	BARNES
3		AA	LONDON
4		AA	PUTNEY
5				=VLOOKUP(A2,B2:C4,2,FALSE)
6				BARNES
Sheet1

 

[barry houdini]

You could try this array formula which must be confirmed with CTRL+SHIFT+ENTER

=INDEX(C2:C4,SMALL(IF(A2=B2:B4,ROW(B2:B4)-ROW(A1)),3))

The three at the end is effectively the occurrence number

 

[delamere]

Thank you VERY much for your help. Your formula is mind boggling for a novice like me!!
I wanted it to complete a table such as the one below.
I have added to your formula to enable blanks to be shown when values are not found.
This is an example of what I wanted, and now can, achieve.
The formula I finished up with (cell B2) is
'IF(ISERROR(INDEX($E$2:$E$1000,SMALL(IF($A2=$D$2:$D$1000,ROW($D$2:$D$1000)-ROW($A$1)),G2))),"",INDEX($E$2:$E$1000,SMALL(IF($A2=$D$2:$D$1000,ROW($D$2:$D$1000)-ROW($A$1)),G2)))
A bit long but it works!
THANK YOU again.

MREX100505 Sample DrExcel sheet.xls
	A	B	C	D	E	F	G
2	AAA	BILL		AAA	BILL		1
3		JANE		AAA	JANE		2
4		FRED		AAA	FRED		3
5				BBB	PAUL		4
6	BBB	PAUL		CCC	DAVID		1
7				CCC	BARRY		2
8				CCC	GEMMA		3
9				CCC	JO		4
10	CCC	DAVID		DDD	ROBERT		1
11		BARRY		DDD	LOUISE		2
12		GEMMA					3
13		JO					4
14	DDD	ROBERT					1
15		LOUISE					2
16							3
17							4
Sheet17