Using Index, Match and Small to Populate Data from a Data Page

[NateN]

Good Day,

I'm trying to figure out how to pull data from my raw data page (Screenshot 1) and transfer certain data to the second spreadsheet (Screenshot 2). I'm having troubles with getting my formula to populate the correct run number.

I'm trying to use Index match function. If I select company 1 then it should populate all of company 1's transactions. But when company one has more than 1 transaction on the same day, the formula doesn't seem to pick up the next #.

I've been playing around with the formula but I can't seem to get it to work.

Any help would be greatly appreciated.


Excel 2010

	A	B	C	D	E	F
Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]28-Apr-17
[/TD]
[TD="align: right"]1234567
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]($72,526.00)
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]28-Apr-17
[/TD]
[TD="align: right"]1234568
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,526.00
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234569
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234570
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]25-May-17
[/TD]
[TD="align: right"]1234571
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($1,000.00)
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5-Apr-17
[/TD]
[TD="align: right"]1234572
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234573
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]($500.00)
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234574
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]($500.00)
[/TD]

</tbody>

Sheet1​

Excel 2010

	A	B
1
28-Apr-17	1234567
29-Apr-17	1234573
29-Apr-17	1234573

<tbody>
[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #F2F2F2"]Date
[/TD]
[TD="bgcolor: #F2F2F2"]Run #
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: center"]12
[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A10
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$8, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A10)),1),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B10
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A10),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(2:2)),4),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A11
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$9, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A11)),1),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A11),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(3:3)),4),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A12
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$9, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A12)),1),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B12
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A12),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(4:4)),4),"")}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Oh those numbers are just examples. Trying to work out the kinks in the spreadsheet. Essentially, sometimes they will take on a liability in which case it will be a positive value for the one company. If they transferred it away then it will be a negative. If company 1 for example is in the transferee column then the whatever values that are in asset and liabilities should be the opposite sign.

So is it safe to say that in Sheet3, all of the numbers in the Assets column should be positive and all of the values in the Liabilities column should be negative?

 

[NateN]

So is it safe to say that in Sheet3, all of the numbers in the Assets column should be positive and all of the values in the Liabilities column should be negative?

Not always the case. I'll explain it better. This is my fault Friday brain.

If the company is in the transferor column in sheet1 then the assets and liability will be negative in sheet3. But if the same company is in the transferee column in sheet 1, then in sheet3, assets should be positive and liabilities be positive. I apologize as I did not do a good job in explaining that before. Essentially it is tracking all the assets and liabilities as a hard number if its either going in (Positive) or leaving the company (negative). I understand that usually assets are positive and liabilities are negative but in this circumstance it's a little different since it is between two parties.

 

[63falcondude]

In sheet 3, D5 should be a negative number since Company 1 is in the transferee column in Sheet 1.

If the company is in the transferor column in sheet1 then the assets and liability will be negative in sheet3. But if the same company is in the transferee column in sheet 1, then in sheet3, assets should be positive and liabilities be positive.

Just trying to wrap my head around this. Are you not contradicting yourself here? Company 1 is in both Transferor and Transferee columns.

Maybe create another small sample of data along with the desired output of the formula and I can go off of that.

 

[NateN]

Sorry, I can see the confusion now! I fixed the sample data to be more relevant.

Please see below

Excel 2010

	A	B	C	D	E	F
Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28-Apr-17[/TD]
[TD="align: right"]1234567[/TD]
[TD="align: right"]($2,000.00)[/TD]
[TD="align: right"]($100.00)[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27-Apr-17[/TD]
[TD="align: right"]1234568[/TD]
[TD="align: right"]($500.00)[/TD]
[TD="align: right"]($500.00)[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]25-Apr-17[/TD]
[TD="align: right"]1234569[/TD]
[TD="align: right"]$0.00 [/TD]
[TD="align: right"]$0.00 [/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C	D
1	Month	April

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #F2F2F2"]Date[/TD]
[TD="bgcolor: #F2F2F2"]Transfer/Run #[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Assets[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Liabilites [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]28-Apr-17[/TD]
[TD="align: right"]1234567[/TD]
[TD="align: right"] (2,000.00)[/TD]
[TD="align: right"] (100.00)[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]27-Apr-17[/TD]
[TD="align: right"]1234568[/TD]
[TD="align: right"] (500.00)[/TD]
[TD="align: right"] (500.00)[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Row 6 should have positive asset and liabilities since company 1 is in the transferee column (column B) in sheet1. Row 5 is correct since company 1 is in the transferor column (Column A) in sheet1

 

[63falcondude]

How about this? I included a multiplication factor of -1 if the company was found in the "Transferee" column.

Excel 2010
	A	B	C	D	E	F
1	Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities
2	1	2	28-Apr-17	1234567	($2,000.00)	($100.00)
3	2	1	27-Apr-17	1234568	($500.00)	($500.00)
4	3	6	25-Apr-17	1234569	$0.00	$0.00
Sheet1

Excel 2010
	A	B	C	D
1
2	1
3	Date	Transfer/Run #	Assets	Liabilites
4	28-Apr-17	1234567	($2,000.00)	($100.00)
5	27-Apr-17	1234568	$500.00	$500.00
Sheet3

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX(Sheet1!C$2:C$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
C4	{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$4,$A$2,Sheet1!$C$2:$C$4,$A4,Sheet1!$D$2:$D$4,$B4),-1,1)*INDEX(Sheet1!E$2:E$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[NateN]

How about this? I included a multiplication factor of -1 if the company was found in the "Transferee" column.

Excel 2010

	A	B	C	D	E	F

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Transferor[/TD]
[TD="align: center"]Transferee[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Transfer[/TD]
[TD="align: center"]Deemed Assets[/TD]
[TD="align: center"]Deemed Liabilities[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]28-Apr-17[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]($2,000.00)[/TD]
[TD="align: center"]($100.00)[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]27-Apr-17[/TD]
[TD="align: center"]1234568[/TD]
[TD="align: center"]($500.00)[/TD]
[TD="align: center"]($500.00)[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25-Apr-17[/TD]
[TD="align: center"]1234569[/TD]
[TD="align: center"]$0.00 [/TD]
[TD="align: center"]$0.00 [/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C	D

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Transfer/Run #[/TD]
[TD="align: center"]Assets[/TD]
[TD="align: center"]Liabilites[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]28-Apr-17[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]($2,000.00)[/TD]
[TD="align: center"]($100.00)[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]27-Apr-17[/TD]
[TD="align: center"]1234568[/TD]
[TD="align: center"]$500.00 [/TD]
[TD="align: center"]$500.00 [/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!C$2:C$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$4,$A$2,Sheet1!$C$2:$C$4,$A4,Sheet1!$D$2:$D$4,$B4),-1,1)*INDEX(Sheet1!E$2:E$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$4,$A$2,Sheet1!$C$2:$C$4,$A4,Sheet1!$D$2:$D$4,$B4),-1,1)*INDEX(Sheet1!E$2:E$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}

Should the underline in the formula be referenced for Sheet3?

 

[63falcondude]

{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$4,$A$2,Sheet1!$C$2:$C$4,$A4,Sheet1!$D$2:$D$4,$B4),-1,1)*INDEX(Sheet1!E$2:E$4,SMALL(IF(Sheet1!$A$2:$B$4=Sheet1!$A$2,ROW(Sheet1!$A$2:$A$4)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}

Should the underline in the formula be referenced for Sheet3?

Yes. Good catch. That's my mistake.

 

[NateN]

Yes. Good catch. That's my mistake.

Not a problem at all. Thank you so much for all your help. My apologies for the confusion before.

 

[63falcondude]

Not a problem at all. Thank you so much for all your help. My apologies for the confusion before.

No problem, did it work for you?

 

[NateN]

Hey,

I seem to get an error when I tried to copy all the way down. Please see screenshot.

Excel 2010

	A	B	C	D	E	F	G
Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities	Month
April
April
April
April
May
April
April
April

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]28-Apr-17
[/TD]
[TD="align: right"]1234567
[/TD]
[TD="align: right"]($2,000.00)
[/TD]
[TD="align: right"]($100.00)
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]27-Apr-17
[/TD]
[TD="align: right"]1234568
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($500.00)
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234569
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234570
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]25-May-17
[/TD]
[TD="align: right"]1234571
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($1,000.00)
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5-Apr-17
[/TD]
[TD="align: right"]1234572
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234573
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($500.00)
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234574
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($500.00)
[/TD]

</tbody>

Sheet1
Excel 2010

	A	B	C	D
1	Month	May

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #F2F2F2"]Date[/TD]
[TD="bgcolor: #F2F2F2"]Transfer/Run #[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Assets[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Liabilites [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25-May-17[/TD]
[TD="align: right"]1234571[/TD]
[TD="align: right"] $ (2,000.00)[/TD]
[TD="align: right"] $ (100.00)[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] $ (500.00)[/TD]
[TD="align: right"] $ (500.00)[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] $ (500.00)[/TD]
[TD="align: right"] $ (1,000.00)[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] $ (500.00)[/TD]
[TD="align: right"] $ (500.00)[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] $ (500.00)[/TD]
[TD="align: right"] $ (500.00)[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$100,$A$2,Sheet1!$C$2:$C$100,$A5,Sheet1!$D$2:$D$100,$B5),-1,1)*INDEX(Sheet1!E$2:E$100,SMALL(IF(Sheet1!$A$2:$B$100=Sheet3!$A$2,ROW(Sheet1!$A$2:$A$100)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

​

When I go to select the Month of May. April submissions are still there and the Row 5, Sheet3 is not populating the correct numbers. Sorry to keep on bugging you but do you happen to have a way around this?

​