Vlook-up Formula
 

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.

Vlook-up Formula
 

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.