index_match

[via135]

hi all

i am having A11 in sheet1 headers

month year basic da

A2:C32 data accordingly.

in Sheet2 A1:L1 headers as

BASIC Nov 07 - Jan 08 feb 08-apr 08 may 08-jul 08 aug 08-oct 08 nov 08-
jan 09 feb 09-apr 09 may 09-jul 09 aug 09-oct 09 nov 09-jan 10 feb 10-
apr 10 may 10-jul 10

A2:L11 data accordingly, where A2:A11 is having BASIC and
B2:L11 is having DA corresponding to the periods B1:L1.

now what i want is to pull the DA figures in Sheet2 to
Col D in Sheet1 corresponding to the MONTH, YEAR & BASIC
in Col A, Col B and Col C respectively..!

how can i achieve this through Worksheet function..?!

any help pl..!

thanks and regards!

-via135

 

[Ms-Exl-Learner]

hi all

i am having A11 in sheet1 headers

month    year       basic        da

A2:C32 data accordingly.

in Sheet2 A1:L1 headers as

BASIC   Nov 07 - Jan 08 feb 08-apr 08   may 08-jul 08   aug 08-oct 08   nov 08-
jan 09  feb 09-apr 09   may 09-jul 09   aug 09-oct 09   nov 09-jan 10   feb 10-
apr 10  may 10-jul 10

A2:L11 data accordingly, where A2:A11 is having BASIC and
B2:L11 is having DA corresponding to the periods B1:L1.

now what i want is to pull the DA figures in Sheet2 to
Col D in Sheet1 corresponding to the MONTH, YEAR & BASIC
in Col A, Col B and Col C respectively..!

how can i achieve this through Worksheet function..?!

any help pl..!

thanks and regards!

-via135

Copy and paste the below formula in B4 Cell of Sheet2:-
=IF(B$1="","",DATE(YEAR(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B
$1))-1)))),MONTH(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B$1))-1)))),
1))
Drag The Sheet2 B4 cell formula upto L4 cell.

For Deriving Basic from Sheet2:-
Copy and paste the below formula in C2 Cell of Sheet1:-
=IF(ISNA(MATCH(DATE(B2,MONTH(TRIM(A2)&1),1),Sheet2!$B$4:$L
$4,1)),"",INDEX(Sheet2!$B$2:$L$2,MATCH(DATE(B2,MONTH(TRIM(A2)&1),
1),Sheet2!$B$4:$L$4,1)))

For Deriving DA from Sheet2:-
Copy and paste the below formula in D2 Cell of Sheet1:-
=IF(ISNA(MATCH(DATE(B2,MONTH(TRIM(A2)&1),1),Sheet2!$B$4:$L
$4,1)),"",INDEX(Sheet2!$B$3:$L$3,MATCH(DATE(B2,MONTH(TRIM(A2)&1),
1),Sheet2!$B$4:$L$4,1)))

Select the Sheet1-C2 and D2 cells and Drag it to the remaining cells
of that column based on the A & B Column Data.

Hope it’s clear!!!

 

[via135]

Copy and paste the below formula in B4 Cell of Sheet2:-
=IF(B$1="","",DATE(YEAR(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B
$1))-1)))),MONTH(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B$1))-1)))),
1))
Drag The Sheet2 B4 cell formula upto L4 cell.

For Deriving Basic from Sheet2:-
Copy and paste the below formula in C2 Cell of Sheet1:-
=IF(ISNA(MATCH(DATE(B2,MONTH(TRIM(A2)&1),1),Sheet2!$B$4:$L
$4,1)),"",INDEX(Sheet2!$B$2:$L$2,MATCH(DATE(B2,MONTH(TRIM(A2)&1),
1),Sheet2!$B$4:$L$4,1)))

For Deriving DA from Sheet2:-
Copy and paste the below formula in D2 Cell of Sheet1:-
=IF(ISNA(MATCH(DATE(B2,MONTH(TRIM(A2)&1),1),Sheet2!$B$4:$L
$4,1)),"",INDEX(Sheet2!$B$3:$L$3,MATCH(DATE(B2,MONTH(TRIM(A2)&1),
1),Sheet2!$B$4:$L$4,1)))

Select the Sheet1-C2 and D2 cells and Drag it to the remaining cells
of that column based on the A & B Column Data.

Hope it’s clear!!!

#######Copy and paste the below formula in B4 Cell of Sheet2:-
=IF(B$1="","",DATE(YEAR(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B
$1))-1)))),MONTH(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B$1))-1)))),
1))
Drag The Sheet2 B4 cell formula upto L4 cell. ##########

1) I have told that in Sheet2 headers are through A1:L1 and
data through A2:L11


########Select the Sheet1-C2 and D2 cells and Drag it to the remaining
cells
of that column based on the A & B Column Data. #######

2) similarly in Sheet2, A11 there are headers (A1 "MONTH", B1
"YEAR(yyyy)", C1 "BASIC" & D1 "DA")
and data through A2:C32 in corresponding columns.


-via135

 

[Ms-Exl-Learner]

#######Copy and paste the below formula in B4 Cell of Sheet2:-
=IF(B$1="","",DATE(YEAR(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B
$1))-1)))),MONTH(VALUE(TRIM(LEFT(TRIM(B$1),FIND("-",TRIM(B$1))-1)))),
1))
Drag The Sheet2 B4 cell formula upto L4 cell. ##########

1)     I have told that in Sheet2 headers are through A1:L1 and
data through A2:L11

########Select the Sheet1-C2 and D2 cells and Drag it to the remaining
cells
of that column based on the A & B Column Data. #######

2)        similarly in Sheet2, A11 there are headers (A1 "MONTH", B1
"YEAR(yyyy)", C1 "BASIC" & D1 "DA")
 and data through A2:C32 in corresponding columns.

-via135

Click or Copy and paste the below link in web browser to download the
Example File.

http://www.sendspace.com/file/qhvf9r

Scroll the mouse button to the bottom of the website and click
Download.

 

[via135]

Click or Copy and paste the below link in web browser to download the
Example File.

http://www.sendspace.com/file/qhvf9r

Scroll the mouse button to the bottom of the website and click
Download.

hi..!

in your sample file i am afraid that
the solution what i want is not available.
may be i haven't explained much..!

anyhow, what i have done now is that
i have edited your sample file by inserting
worksheets and give my data in
Sheet3 & Sheet4. Hence please read
Sheet1 & Sheet2 in my original post
as Sheet3 & Sheet4 respectively.

Now what i want is to pull the
DA amounts in Sheet3, Col D
corresponding to the MONTH (Col A), YEAR (Col B)
and BASIC (Col C) from the data available in
Sheet4 BASIC (Col A) and PERIOD (QUARTERLY)
in the Row B1:L1..!

Hope i have explained enough..!!


the link for the edited sample file is given below:
http://www.sendspace.com/file/vuixao


help please..!

-via135

 

[Ms-Exl-Learner]

Click or Copy and paste the below link in web browser to download the
Example File.

http://www.sendspace.com/file/2jiskz

Scroll the mouse button to the bottom of the website and click
Download.

File Name: Revised SOLUTION (WORKING WITH DATE).xls

Have a look in the Sheet3 & Sheet4 worksheets.