index_match

  • Thread starter Thread starter via135
  • Start date Start date
V

via135

hi all

i am having A1:D1 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
 
hi all

i am having A1:D1 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
Click to expand...



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.

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!!!
Click to expand...

#######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, A1:D1 there are headers (A1 "MONTH", B1
"YEAR(yyyy)", C1 "BASIC" & D1 "DA")
and data through A2:C32 in corresponding columns.


-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. ##########

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, A1:D1 there are headers (A1 "MONTH", B1
"YEAR(yyyy)", C1 "BASIC" & D1 "DA")
 and data through A2:C32 in corresponding columns.

-via135
Click to expand...



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.
 
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.
Click to expand...

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
 
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.
 
Back
Top