Vlookup with specific row count

[Shaza]

Hi, is it possible to combine vlookup with get value in specific row. Do i need to do macro behind or it can be done with cell formula only. When using vlookup only value in first row retrieve.Example as
=VLOOKUP(B4,B3:C9,2,FALSE)
In scenario below, i want to get payment date result based on payment received, such for example for ID 123 which suppose go to cell second payment date instead of first date payment.

Any help is appreciated.


DATA

[TABLE="width: 252"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD] ID[/TD]
[TD] Payment Date[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]01/01/2002[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]02/01/2002[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]05/02/2002[/TD]
[/TR]
[TR]
[TD]Suzy[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]16/03/2002[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]25/03/2002[/TD]
[/TR]
[TR]
[TD]Suzy[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]08/04/2002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name [/TD]
[TD] ID[/TD]
[TD]Payment Received[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Suzy[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

OUTCOME

[TABLE="width: 150"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Report[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD="align: right"]01/01/2002[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

this will work if the payment dates are in order as shown

Book1
	A	B	C
1	Sheet1
2	Name	ID	Payment Date
3	Mike	123	01/01/2002
4	Jason	111	02/01/2002
5	Mike	123	05/02/2002
6	Suzy	222	16/03/2002
7	Mike	123	25/03/2002
8	Suzy	222	08/04/2002
9
10	Sheet2
11	Name	ID	Payment Received
12	Mike	123	2
13	Jason	111	1
14	Suzy	222	2
15
16
17	OUTCOME
18
19	Report
20	123	05/02/2002
21	111	02/01/2002
22	222	08/04/2002
Sheet1

Cell Formulas
Range	Formula
B20	{=SMALL(IF($B$3:$B$8=A20,$C$3:$C$8),C12)}
Press CTRL+SHIFT+ENTER to enter array formulas.