Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLook UP formula question | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLook Up Formula | Excel Discussion (Misc queries) | |||
vlook up and if formula | Excel Worksheet Functions |