match dates

[ehsas]

Book1
	A	B	C	D	E
1	DAYSINCEA/COPEN			PAYMENDATE	AMOUNT
2	1-Jan-02			3-Jan-02	200
3	2-Jan-02			5-Jan-02	500
4	3-Jan-02			6-Jan-02	400
5	4-Jan-02			8-Jan-02	300
6	5-Jan-02			9-Jan-02	200
7	6-Jan-02			11-Jan-02	800
8	7-Jan-02			13-Jan-02	200
9	8-Jan-02
10	9-Jan-02
11	10-Jan-02
12	11-Jan-02
13	12-Jan-02
14	13-Jan-02
15	14-Jan-02
Sheet1

Refer to above excel sheet i need to move the payment dates with amount to the column B infront of exact date of column a means 3 jan with amount of 200 has to come to b4.Please if anyone could help writing formula or macro.

Thanks

 

[ehsas]

=IF(ISNA(SETV VLOOKUPA2,D$2:E$8,2,0))),0,GETV())

I am using above formula but it not formating the dates as date it giving as number can I format the date also sametime.Regards,

 

[Aladin Akyurek]

On 2002-09-21 11:23, ehsas wrote:
=IF(ISNA(SETV VLOOKUPA2,D$2:E$8,2,0))),0,GETV())

I am using above formula but it not formating the dates as date it giving as number can I format the date also sametime.Regards,

Watch out: you're missing some parens there...

=IF(ISNA(SETV(VLOOKUP(A2,D$2:E$8,2,0))),0,GETV())

Custom format the formula cell as e.g.,

[>0]dd-mmm-yy

which is needed because of 0's the formula will also return.

 

[ehsas]

how to do that.Could you write the formula please.Regards,Also be noted that there is amount also which is not date.Regards,

 

[Aladin Akyurek]

On 2002-09-21 11:52, ehsas wrote:
how to do that.Could you write the formula please.Regards,Also be noted that there is amount also which is not date.Regards,

What does the formula return besides 0's -- dates or amounts? It should not be amounts as well as dates within the same range.