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

N

NateN

New Member
Joined
Apr 4, 2017
Messages
18
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
1
Transferor
Transferee
Date
Transfer
Deemed Assets
Deemed Liabilities
2
1
2
28-Apr-17
1234567
$0.00
($72,526.00)
3
2
1
28-Apr-17
1234568
$0.00
$72,526.00
4
3
6
25-Apr-17
1234569
$0.00
$0.00
5
4
15
25-Apr-17
1234570
$0.00
$0.00
6
4
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)

<tbody>
</tbody>
Sheet1​





Excel 2010
A
B
3
1
4
Date
Run #
10
28-Apr-17
1234567
11
29-Apr-17
1234573
12
29-Apr-17
1234573

<tbody>
</tbody>
Sheet3​


Array Formulas
Cell
Formula
A10
{=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),"")}
B10
{=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),"")}
A11
{=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),"")}
B11
{=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),"")}
A12
{=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),"")}
B12
{=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),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Sort by date Sort by votes
NateN said:
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?
Click to expand...

No problem. Give this a whirl:


Excel 2010
ABCDEFG
1TransferorTransfereeDateTransferDeemed AssetsDeemed LiabilitiesMonth
21228-Apr-171234567($2,000.00)($100.00)April
32127-Apr-171234568($500.00)($500.00)April
43625-Apr-171234569$0.00$0.00April
541525-Apr-171234570$0.00$0.00April
61525-May-171234571($500.00)($1,000.00)May
7545-Apr-171234572$0.00$0.00April
81229-Apr-171234573($500.00)($500.00)April
91829-Apr-171234574($500.00)($500.00)April
Sheet1



Excel 2010
ABCD
1
21MonthMay
3DateTransfer/Run #AssetsLiabilites
425-May-171234571-500-1000
5
Sheet3
Cell Formulas
RangeFormula
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.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Awesome. This worked like a charm. Thanks for all your help! I hope it goes smoothly from here on out.
 
Upvote 0
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
ABCD
21Month May
3DateTransfer/Run #AssetsLiabilites
4
527-Apr-171234568 $ 500.00 $ 500.00
625-May-171234571 $ (500.00)-1000

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
NateN said:
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.
Click to expand...


Excel 2010
ABCDEF
1TransferorTransfereeDateTransferDeemed AssetsDeemed Liabilities
21228-Apr-171234567($2,000.00)($100.00)
32127-Apr-171234568($500.00)($500.00)
43625-Apr-171234569$0.00$0.00
541525-Apr-171234570$0.00$0.00
61525-May-171234571($500.00)($1,000.00)
7545-Apr-171234572$0.00$0.00
81229-Apr-171234573($500.00)($500.00)
91829-Apr-171234574($500.00)($500.00)
Sheet1



Excel 2010
ABCD
1
21MonthMay-17
3DateTransfer/Run #AssetsLiabilites
428-Apr-171234567-2000-100
527-Apr-171234568500500
625-May-171234571-500-1000
729-Apr-171234573-500-500
829-Apr-171234574-500-500
9
Sheet3
Cell Formulas
RangeFormula
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").
 
Upvote 0
If you wanted to manually mark which rows should be pulled to Sheet3, you can try this:


Excel 2010
ABCDEFG
1TransferorTransfereeDateTransferDeemed AssetsDeemed LiabilitiesCarryover
21228-Apr-171234567($2,000.00)($100.00)Yes
32127-Apr-171234568($500.00)($500.00)Yes
43625-Apr-171234569$0.00$0.00
541525-Apr-171234570$0.00$0.00
61525-May-171234571($500.00)($1,000.00)Yes
7545-Apr-171234572$0.00$0.00
81229-Apr-171234573($500.00)($500.00)Yes
91829-Apr-171234574($500.00)($500.00)Yes
Sheet1



Excel 2010
ABCD
1
21
3DateTransfer/Run #AssetsLiabilites
428-Apr-171234567-2000-100
527-Apr-171234568500500
625-May-171234571-500-1000
729-Apr-171234573-500-500
829-Apr-171234574-500-500
9
Sheet3
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
You answered my question before I even posted it! Thanks! I'll try this and let you know
 
Last edited:
Upvote 0
63falcondude said:
If you wanted to manually mark which rows should be pulled to Sheet3, you can try this:

Excel 2010
ABCDEFG
1TransferorTransfereeDateTransferDeemed AssetsDeemed LiabilitiesCarryover
21228-Apr-171234567($2,000.00)($100.00)Yes
32127-Apr-171234568($500.00)($500.00)Yes
43625-Apr-171234569$0.00 $0.00
541525-Apr-171234570$0.00 $0.00
61525-May-171234571($500.00)($1,000.00)Yes
7545-Apr-171234572$0.00 $0.00
81229-Apr-171234573($500.00)($500.00)Yes
91829-Apr-171234574($500.00)($500.00)Yes

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



Excel 2010
ABCD
1
21
3DateTransfer/Run #AssetsLiabilites
428-Apr-171234567-2000-100
527-Apr-171234568500500
625-May-171234571-500-1000
729-Apr-171234573-500-500
829-Apr-171234574-500-500
9

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3​

Array Formulas
CellFormula
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))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...

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".
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
INDEX Functions with multiple criteria
Replies
5
Views
274
Phuoc
P
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
168
SunnyAlv
SunnyAlv
S
  • Solved
Problem modifying INDEX formula
Replies
1
Views
183
MountainFog
M
T
  • Question
Pull data from multiple sheets that match varying criteria
Replies
0
Views
96
T1991
T
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
556
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,761
Messages
6,126,735
Members
449,333
Latest member
Adiadidas

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top