How to get data from other sheet with 2 horizontal criteral and 1 vertical criteria in other sheet

[mart_mrexcel]

How can i get the Actual amount (in Sheet1 Template) with 2 criteria as Horizontal and 1 criteria as vertical in Sheet2 (i.e. my Master Data). See below sample

Template in Sheet1

Month	CC	GL Codes	Actual Amount
April-13	FIN	4200010	55
April-13	HR	4200020	648
Feb-13	MFG	4200030	489
April-13	MFG	4200040	1563
April-13	SALES	4200020	260

<TBODY>
</TBODY>

Data in Sheet2

Month	CC	4200010	4200020	4200030	4200040
April-13	SALES	2657	260	564	1500
April-13	HR	10	648	223	655
April-13	FIN	55	9876	10000	689
April-13	MFG	66	6543	5000	1563
Feb-13	HR	69	65	664	15687
Feb-13	FIN	29	64	698	2648
Feb-13	MFG	69	49	489	2546

<TBODY>
</TBODY>

 

[Aladin Akyurek]

C2, Sheet2, control+shift+enter, not just enter, copy across, and down:

=INDEX(Sheet1!$D$2:$D$6,MATCH(1,IF(Sheet1!$A$2:$A$6=$A2,
  IF(Sheet1!$B$2:$B$6=$B2,IF(Sheet1!$C$2:$C$6=C$1,1))),0))

 

[vds1]

In Sheet1 D2

 

[mart_mrexcel]

Thanks for the reply, but i am getting #N/A. Actually i need the formula to be in sheet1 under the "Actual Amount" and drag it down (with CSE)

 

[Aladin Akyurek]

Thanks for the reply, but i am getting #N/A. Actually i need the formula to be in sheet1 under the "Actual Amount" and drag it down (with CSE)

D2, Sheet1, control+shift+enter, not just enter, and copy down...

=INDEX(Sheet2!$C$2:$F$8,MATCH(1,IF(Sheet2!$A$2:$A$8=$A2,
  IF(Sheet2!$B$2:$B$8=$B2,1)),0),MATCH($C2,Sheet2!$C$1:$F$1,0))

 

[vds1]

Sheet1 D2

=INDEX(Sheet2!$A$1:$F$8,SUMPRODUCT((Sheet2!$A$1:$A$8=A2)*(Sheet2!$B$1:$B$8=B2)*ROW(Sheet2!$A$1:$A$8)),MATCH(C2,Sheet2!$1:$1))

i am assuming sheet2 is from Range A1 to F8

Or if range is huge then use

=INDEX(Sheet2!A:F,SUMPRODUCT((Sheet2!A:A=A2)*(Sheet2!B:B=B2)*ROW(Sheet2!A:A)),MATCH(C2,Sheet2!$1:$1))

 

[mart_mrexcel]

Thanks Mr. Aladin that works perfectly okay. have a great day!

 

[mart_mrexcel]

Sheet1 D2

=INDEX(Sheet2!$A$1:$F$8,SUMPRODUCT((Sheet2!$A$1:$A$8=A2)*(Sheet2!$B$1:$B$8=B2)*ROW(Sheet2!$A$1:$A$8)),MATCH(C2,Sheet2!$1:$1))

i am assuming sheet2 is from Range A1 to F8

Or if range is huge then use

=INDEX(Sheet2!A:F,SUMPRODUCT((Sheet2!A:A=A2)*(Sheet2!B:B=B2)*ROW(Sheet2!A:A)),MATCH(C2,Sheet2!$1:$1))

Hi Mr. Vds1 thanks for your reply.. i am also trying your formula ... but i am getting #N/A

 

[mart_mrexcel]

D2, Sheet1, control+shift+enter, not just enter, and copy down...

=INDEX(Sheet2!$C$2:$F$8,MATCH(1,IF(Sheet2!$A$2:$A$8=$A2,
  IF(Sheet2!$B$2:$B$8=$B2,1)),0),MATCH($C2,Sheet2!$C$1:$F$1,0))

Mr. Aladin, just a follow up .... I cannot drag it down (CSE) i am receiving a message that "You cannot change part of an array"...

 

[Aladin Akyurek]

Thanks Mr. Aladin that works perfectly okay. have a great day!

You are welcome.

Mr. Aladin, just a follow up .... I cannot drag it down (CSE) i am receiving a message that "You cannot change part of an array"...

You must enter the formula in the appropriate cell, apply control+shift+enter, then drag it down.

I guess you dragged it first then applied control+shift+enter. That creates an array area you cannot change. If so, delete this area entirely and implement the formula the proper way (see above).