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]

For cell A4, should there also be a criteria for the company? Or is this all overridden since now we have a carryover column that says "Yes".

The latter. This formula will only pull results with a "Yes" in column G so there is no need to specify date range or company number.

 

[NateN]

It works perfectly. For the deemed assets and liability, it was producing a number that wouldn't belong to the company number. So adding in that criteria should fix the problem?

 

[63falcondude]

It works perfectly. For the deemed assets and liability, it was producing a number that wouldn't belong to the company number. So adding in that criteria should fix the problem?

I'm not sure what you mean. The formula that only looks for "Yes" in Sheet1 column G does just that.

If there is a "Yes" in column G then the values in that row will be brought over to Sheet3.

The assets and liabilities columns will then be multiplied by 1 or -1 depending on which column the company number is in. This is the only part that looks for the company number.

 

[NateN]

I'm not sure what you mean. The formula that only looks for "Yes" in Sheet1 column G does just that.

If there is a "Yes" in column G then the values in that row will be brought over to Sheet3.

The assets and liabilities columns will then be multiplied by 1 or -1 depending on which column the company number is in. This is the only part that looks for the company number.

It all makes sense. I was just referencing the wrong cell. It works now! Thanks again for all your help! I don't think I could have gotten this far without your help. So thank you!

 

[63falcondude]

It all makes sense. I was just referencing the wrong cell. It works now! Thanks again for all your help! I don't think I could have gotten this far without your help. So thank you!

You're very welcome. Glad to help.