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

#### mart_mrexcel

##### Active Member
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>

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
C2, Sheet2, control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
``````=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))``````

In Sheet1 D2

Last edited:

#### mart_mrexcel

##### Active Member
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)

##### MrExcel MVP

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...
Rich (BB code):
``````=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

##### Well-known Member
Sheet1 D2

Code:
``=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

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

Last edited:

#### mart_mrexcel

##### Active Member

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

#### mart_mrexcel

##### Active Member
Sheet1 D2

Code:
``=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

Code:
``=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

##### Active Member
D2, Sheet1, control+shift+enter, not just enter, and copy down...
Rich (BB code):
``````=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"...

##### MrExcel MVP
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).

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,787
Members
430,569
Latest member
sbardelli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back