Please don't double-post - you have an answer elsewhere.

Pete

On Dec 29, 9:35*am, K****ij wrote:
Hi everyone....
In Excel we have data in sheet 1 as follows
* * * * A * * * * * * * * * * * B * * * * * * * * * C * *D * * * * * * E *
1 * * * * Index No. * * * * * * * * * * Date * * * * * * *Blank * * *Code * *
Reference *
2 * * * * 1208-2134706406 * * * * 12/19/2008 * * * * * * * 0100 * * *G
11/11/08-12/12/08
3 * * * * 1208-2134706406 * * * * 12/19/2008 * * * * * * * 0100 * * *E
11/11/08-12/12/08
4 * * * * 1208-2134726210 * * * * 12/18/2008 * * * * * * * 0100 * * *G
11/11/08-12/12/08
5 * * * * 1208-2134726210 * * * * 12/18/2008 * * * * * * * 0100 * * *E
11/11/08-12/12/08

* * * * * * * * F * * * * * * * * * *G * * * * * * * * * H * * * * * * * *I *
* * * * * * * *J
1 * * * * Relation * * * Relation code * * *Amount * * *Discount * * net
amount
2 * * * * * * *R * * * * * * 70003-00 * * * * *1,256.32 * * * 0.00 * * * * *
1,256.32
3 * * * * * * *R * * * * * * 70002-00 * * * * *1,755.64 * * * 0.00 * * * * *
1,755.64
4 * * * * * * *R * * * * * * 70003-00 * * * * * * 500.76 * * * 0.00 * * * * *
* * 500.76
5 * * * * * * *R * * * * * * 70002-00 * * * * * * 780.75 * * * 0.00 * * * * *
* * 780.75

* * * * * * * * K * * * * * * * * L
1 * * * *R. Date * * * * *Target
2
3 * * * *12/23/2008 * *1223
4 * * * *12/24/2008 * *4558
5

In Sheet 2, we have to fill up data specifically for Reference starting with
"E"
* * * * * A * * * * * * * * * * B * * * * * * * * *C * * * * * * * * D * * * * *E * * * * * F
1 * *Index No. * * * * * * * *Start Date * * *End Date * * *Blank * * Blank *
*Target
2 * *1208-2134706406
3 * *1208-2134726210

In Sheet 3, we have to fill up data specifically for Reference starting with
"G"

* * * * * A * * * * * * * * * * B * * * * * * * * *C * * * * * * * * D * * * * *E * * * * * F
1 * *Index No. * * * * * * * *Start Date * * *End Date * * *Blank * * Blank *
*Target
2 * *1208-2134726210
3 * *1208-2134706406

The Sheet 2 & 3 are specific, the Sheet 2 contains details for Reference
starting with E, And Sheet 3 contains details of Reference starting with G.
In Column E, in sheet 1, ‘Reference’, has details such as Reference (E or G)
and the start date and end date separated by ‘-‘.

All we need is a V look up formula, to automatically fill in Column F in
Sheet 2 & 3 respectively for Reference (E & G) then Start date in Column B,
and End date in Column C.

We tried the following formula, but could not work properly.
=VLOOKUP($A$2:$A$3,Sheet1!$A$1:$L$5,12,0)

The other data that we tried was having If Condition i.e.
=IF(LEFT(Sheet1!E2,2="E "),VLOOKUP(Sheet2!A2:A3,Sheet1!A1:L5,12,0),"No
target")

Please help me.
Thanks.