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]

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?

No problem. Give this a whirl:

Excel 2010
	A	B	C	D	E	F	G
1	Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities	Month
2	1	2	28-Apr-17	1234567	($2,000.00)	($100.00)	April
3	2	1	27-Apr-17	1234568	($500.00)	($500.00)	April
4	3	6	25-Apr-17	1234569	$0.00	$0.00	April
5	4	15	25-Apr-17	1234570	$0.00	$0.00	April
6	1	5	25-May-17	1234571	($500.00)	($1,000.00)	May
7	5	4	5-Apr-17	1234572	$0.00	$0.00	April
8	1	2	29-Apr-17	1234573	($500.00)	($500.00)	April
9	1	8	29-Apr-17	1234574	($500.00)	($500.00)	April
Sheet1

Excel 2010
	A	B	C	D
1
2	1		Month	May
3	Date	Transfer/Run #	Assets	Liabilites
4	25-May-17	1234571	-500	-1000
5
Sheet3

Cell Formulas
Range	Formula
A4	{=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))),"")}
C4	{=IFERROR(IF(COUNTIFS(Sheet1!$B$2:$B$9,$A$2,Sheet1!$C$2:$C$9,$A4,Sheet1!$D$2:$D$9,$B4),-1,1)*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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[NateN]

Awesome. This worked like a charm. Thanks for all your help! I hope it goes smoothly from here on out.

 

[63falcondude]

Awesome. This worked like a charm. Thanks for all your help! I hope it goes smoothly from here on out.

Whew! Glad to help.

 

[NateN]

Hey Falcon,

Going off your latest post with the most recent formula to pull data. Is it possible to pull a transaction from a previous month based on perhaps adding another parameter. Below is what I'm trying to accomplish.


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"]4[/TD]

[TD="align: center"]5[/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"]6[/TD]
[TD="align: right"]25-May-17[/TD]
[TD="align: right"]1234571[/TD]
[TD="align: right"] $ (500.00)[/TD]
[TD="align: right"]-1000[/TD]

</tbody>

Sheet3​

Even though May is selected, the April 27th transaction should come along. What I'm thinking is that perhaps there should be a column in the source data page called "Carryover" and writing "Yes" for all transactions that need to be carried over. Would this work? And if so how can I make the formula do that or is there an easier way?

Thanks again for all your help.

 

[63falcondude]

Why only the one transaction from April?

If you wanted to create another column in Sheet1 to indicate which rows should be moved to Sheet3, would those "Yes" values be entered manually? If so, that would greatly reduce the complexity of this.

 

[NateN]

It is just an example but there could be multiple transactions from a previous month that will need to be carried over.

Yes, so in Sheet1 the "Yes" value would be entered in manually. It is just trying to set up the formula in sheet 3 to bring over those values even if the month criteria isn't met.

 

[63falcondude]

It is just an example but there could be multiple transactions from a previous month that will need to be carried over.

Yes, so in Sheet1 the "Yes" value would be entered in manually. It is just trying to set up the formula in sheet 3 to bring over those values even if the month criteria isn't met.

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
5	4	15	25-Apr-17	1234570	$0.00	$0.00
6	1	5	25-May-17	1234571	($500.00)	($1,000.00)
7	5	4	5-Apr-17	1234572	$0.00	$0.00
8	1	2	29-Apr-17	1234573	($500.00)	($500.00)
9	1	8	29-Apr-17	1234574	($500.00)	($500.00)
Sheet1

Excel 2010
	A	B	C	D
1
2	1		Month	May-17
3	Date	Transfer/Run #	Assets	Liabilites
4	28-Apr-17	1234567	-2000	-100
5	27-Apr-17	1234568	500	500
6	25-May-17	1234571	-500	-1000
7	29-Apr-17	1234573	-500	-500
8	29-Apr-17	1234574	-500	-500
9
Sheet3

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

These formulas bring over the applicable data from the current and last months. Notice that I have removed the months column from Sheet1 and changed D2 in Sheet3 to a date (by typing in "May 2017").

 

[63falcondude]

If you wanted to manually mark which rows should be pulled to Sheet3, you can try this:

Excel 2010
	A	B	C	D	E	F	G
1	Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities	Carryover
2	1	2	28-Apr-17	1234567	($2,000.00)	($100.00)	Yes
3	2	1	27-Apr-17	1234568	($500.00)	($500.00)	Yes
4	3	6	25-Apr-17	1234569	$0.00	$0.00
5	4	15	25-Apr-17	1234570	$0.00	$0.00
6	1	5	25-May-17	1234571	($500.00)	($1,000.00)	Yes
7	5	4	5-Apr-17	1234572	$0.00	$0.00
8	1	2	29-Apr-17	1234573	($500.00)	($500.00)	Yes
9	1	8	29-Apr-17	1234574	($500.00)	($500.00)	Yes
Sheet1

Excel 2010
	A	B	C	D
1
2	1
3	Date	Transfer/Run #	Assets	Liabilites
4	28-Apr-17	1234567	-2000	-100
5	27-Apr-17	1234568	500	500
6	25-May-17	1234571	-500	-1000
7	29-Apr-17	1234573	-500	-500
8	29-Apr-17	1234574	-500	-500
9
Sheet3

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

 

[NateN]

You answered my question before I even posted it! Thanks! I'll try this and let you know

 

[NateN]

If you wanted to manually mark which rows should be pulled to Sheet3, you can try this:

Excel 2010

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

<colgroup><col><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]
[TD="align: right"][/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: right"][/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: right"][/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
Date	Transfer/Run #	Assets	Liabilites

<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"]4[/TD]
[TD="align: right"]28-Apr-17[/TD]
[TD="align: right"]1234567[/TD]
[TD="align: right"]-2000[/TD]
[TD="align: right"]-100[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]27-Apr-17[/TD]
[TD="align: right"]1234568[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]25-May-17[/TD]
[TD="align: right"]1234571[/TD]
[TD="align: right"]-500[/TD]
[TD="align: right"]-1000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]29-Apr-17[/TD]
[TD="align: right"]1234573[/TD]
[TD="align: right"]-500[/TD]
[TD="align: right"]-500[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]29-Apr-17[/TD]
[TD="align: right"]1234574[/TD]
[TD="align: right"]-500[/TD]
[TD="align: right"]-500[/TD]

[TD="align: center"]9[/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$9,SMALL(IF(Sheet1!$G$2:$G$9="Yes",ROW(Sheet1!$A$2:$A$9)-(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$9,$A$2,Sheet1!$C$2:$C$9,$A4,Sheet1!$D$2:$D$9,$B4),-1,1)*INDEX(Sheet1!E$2:E$9,SMALL(IF(Sheet1!$G$2:$G$9="Yes",ROW(Sheet1!$A$2:$A$9)-(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]

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