Index and If statement assistance

[Killsek]

Hello all,
I am wondering if someone would be able to assist me in getting this Index and If statement to work properly or if there is a better way to do it.
I have two worksheets CR and October, on the CR sheet in Column A is a list of categories (Utilities, Auto Loan, CC, Grocery, etc) and then starting with column D it just shows if its a credit or debit and then continues through like a check register. Now on the October tab in cell H1 you type in the month number (10 in this case) and it will give you all of the listings from CR that is within the month of October and its information based on that entry. ALL OF THIS WORKS FINE !!!
What I cannot get to work is I only want items to show on the October tab if it is in a range from the CR tab in column A10-A12 (and maybe more rows if I add additional categories) so any credits or items other than the cell range A10:A12 then it show up. I added the TRUE/FALSE column to show if it was in the A10:A12 range when I was trying different ways but still couldn't get it to work.

Any ideas would be greatly appreciated..

CR.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	GAS			C/D	Date	Payee	Category	Amount		In A10-A12
2	Other			Debit	10/1/2022	Capital One	CC	$ 225.00		TRUE
3	Entertainment			Debit	10/1/2022	Winn Dixie	Grocery	$ 50.00		FALSE
4	Grocery			Debit	9/25/2022	Discover	Auto Loan	$ 75.00		TRUE
5				Debit	10/2/2022	JEA	Utility	$ 15.00		TRUE
6				Debit	10/3/2022	Racetrack	GAS	$ 38.00		FALSE
7				Debit	10/4/2022	Bojangles	Other	$ 60.00		FALSE
8				Debit	10/30/2022	Mastercard	CC	$ 61.00		TRUE
9				Debit	9/6/2022	Marshalls	Other	$ 62.00		FALSE
10	Auto Loan			Credit	10/7/2022	Paycheck	Deposit		$ 5.00	FALSE
11	CC
12	Utility
13
14
CR

Cell Formulas
Range	Formula
J2:J10	J2	=SUMPRODUCT(--ISNUMBER(SEARCH($A$10:$A$12,$G2)))>0

CR.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Payee	Category	Amount	Deposit		Month #	10
2	10/1/2022	Capital One	CC	$ 225.00	$ -
3	10/1/2022	Winn Dixie	Grocery	$ 50.00	$ -
4	10/2/2022	JEA	Utility	$ 15.00	$ -
5	10/3/2022	Racetrack	GAS	$ 38.00	$ -
6	10/4/2022	Bojangles	Other	$ 60.00	$ -
7	10/30/2022	Mastercard	CC	$ 61.00	$ -
8	10/7/2022	Paycheck	Deposit	$ -	$ 5.00
9
10
11
12
13
14
15
16
October

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR( INDEX(CR!$E$2:$I$3000, SMALL( IF((MONTH(CR!$E$2:$E$3000)=$H$1)*(CR!$E$2:$E$3000>1), ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1 ),ROW(1:1) ),1 ),"" )
B2:B10	B2	=IFERROR( INDEX(CR!$E$2:$I$3000, SMALL( IF((MONTH(CR!$E$2:$E$3000)=$H$1)*(CR!$E$2:$E$3000>1), ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1 ),ROW(1:1) ),2 ),"" )
C2:C10	C2	=IFERROR( INDEX(CR!$E$2:$I$3000, SMALL( IF((MONTH(CR!$E$2:$E$3000)=$H$1)*(CR!$E$2:$E$3000>1), ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1 ),ROW(1:1) ),3 ),"" )
D2:D10	D2	=IFERROR( INDEX(CR!$E$2:$I$3000, SMALL( IF((MONTH(CR!$E$2:$E$3000)=$H$1)*(CR!$E$2:$E$3000>1), ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1 ),ROW(1:1) ),4 ),"" )
E2:E8	E2	=IFERROR( INDEX(CR!$E$2:$I$3000, SMALL( IF((MONTH(CR!$E$2:$E$3000)=$H$1)*(CR!$E$2:$E$3000>1), ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1 ),ROW(1:1) ),5 ),"" )
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

What version of Excel are you using? Please update your profile to show that, as preferred solutions can depend on the version.

 

[jasonb75]

Assuming that you have an up to date version of excel, this will do what you want it to without the need for the helper column (column J) in the CR sheet.

Book1
	A	B	C	D	E	F	G	H
1	Date	Payee	Category	Amount	Deposit		Month #	10
2	10/1/2022	Capital One	CC	$225.00	$0.00
3	10/1/2022	Winn Dixie	Grocery	$50.00	$0.00
4	10/2/2022	JEA	Utility	$15.00	$0.00
5	10/3/2022	Racetrack	GAS	$38.00	$0.00
6	10/4/2022	Bojangles	Other	$60.00	$0.00
7	10/30/2022	Mastercard	CC	$61.00	$0.00
Sheet17

Cell Formulas
Range	Formula
A2:E7	A2	=FILTER(CR!E2:I10,ISNUMBER(MATCH(CR!$G$2:$G$10,CR!$A$1:$A$12,0))*(MONTH(CR!$E$2:$E$10)=$H$1))
Dynamic array formulas.

 

[KRice]

Here is another suggestion if you do not have Excel 365 functions:

MrExcel_20220929.xlsx
	A	B	C	D	E	F	G	H
1	Date	Payee	Category	Amount	Deposit		Month #	10
2	10/1/2022	Capital One	CC	$ 225.00	$ -
3	10/2/2022	JEA	Utility	$ 15.00	$ -
4	10/30/2022	Mastercard	CC	$ 61.00	$ -
5
6
7
October2

Cell Formulas
Range	Formula
A2:E7	A2	=IFERROR(INDEX(CR!$E$2:$I$3000,AGGREGATE(15,6,(ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1)/((MONTH(CR!$E$2:$E$3000)=$H$1)*ISNUMBER(MATCH(CR!$G$2:$G$10,CR!$A$10:$A$12,0))),ROWS($1:1)),COLUMNS($A:A)),"")

 

[KRice]

If you do have Excel 365 functions and you try Jason's solution---and if I understand correctly that you want to include only categories shown in A10:A12---then change CR!$A$1 in Jason's formula to CR!$A$10. That part of the formula describes categories that are to be matched.

 

[jasonb75]

Cheers, Kirk! I misread A10 as A1 in the question.

Little tip for your method, if you use the entire column for the index range you can omit the row adjustments from the array and reduce calculation overheads.

=IFERROR(INDEX(CR!$E:$I,AGGREGATE(15,6,ROW(CR!$E$2:$E$3000)/((MONTH(CR!$E$2:$E$3000)=$H$1)*ISNUMBER(MATCH(CR!$G$2:$G$10,CR!$A$10:$A$12,0))),ROWS($1:1)),COLUMNS($A:A)),"")

The other option to reduce calculation overheads with this method is to make the adjustment outside of the array so that it only needs to make 2 single calculations instead of the ~6000 when working with the array elements directly.

=IFERROR(INDEX(CR!$E$2:$I$3000,AGGREGATE(15,6,ROW(CR!$E$2:$E$3000)/((MONTH(CR!$E$2:$E$3000)=$H$1)*ISNUMBER(MATCH(CR!$G$2:$G$10,CR!$A$10:$A$12,0))),ROWS($1:1))-ROW(CR!$E$2)+1,COLUMNS($A:A)),"")

I did some timed tests on the different methods with some other forum members a couple of years ago, from what I can remember the second method above was slightly faster than the first one (although the first is my personal preference for simplicity) with both offering a substantial improvement over the traditional method.

 

[KRice]

Thanks, Jason...I appreciate the tip. I hadn't thought about either of those approaches. I'm accustomed to forming the row-indexing array {1;2;3;...} with a construction like (ROW(CR!$E$2:$E$3000) - ROW(CR!$E$2)+1), but it's not exactly easy to read and often leaves others wondering what it does. I like the first option too for simplicity and readability.

 

[Killsek]

Thank you Jason and Kirk, you guys have saved me so much time, although I gave it valiant effort I just couldn't get it. I did use Jason's 1st solution as it was the best option for me to utilize.

You guys rock, thanks again !

 

[KRice]

You're welcome...we're happy to help.