Return All instances that match criteria

[shadbolt]

Hi

On sheet1 I have a table.
Column a will be: Income, outgoings, savings, investments, pots
Column b will be any description
Column c - n will be months of the the year
The data is then completed for when bills and money comes in to form a budget

On Sheet2 cell c2 contains the month
I want a new table
Column b to show all incomes
Column c to look up b in bring in values
Only if month matches that where data was entered on sheet 1

i.e In sheet 1 I could put an outgoing cost of £50 for valentines and enter that only for feb
sheet 2 would only bring in that cost if we changed the month to feb

I don't want to use a pivot table or filters.

Thanks in advance

 

[James006]

Hi,

Without a Pivot Table and without Filters ... you are left with Formulas and Array Formulas ...
Besides the fact, one would need to better visualize your sheet, the solution will very much depend on the Excel version you are running ...

 

[shadbolt]

I am using 2016

 

[awoohaw]

If this is new, may I suggest that since your are already recording a description as a column, to also put the date in a column. It by creating a flat file instead of pivoted data (you are planning to pivot by month) you make reporting and charts much more efficient and easier to build formulas with.

But, as @James006 says, more information is helpful

 

[Fluff]

I am using 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[shadbolt]

This will be in data table

EXPENSES.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	LIST ALL OF YOUR EXPENSES HERE
2	In / Out	Description	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
3	In	Wages	2000	2000	2000	2000	2200	2200	2200	2200	2200	2200	2200	2200
4	In	wages 2	1500	1500	700	700	700	700	700	700	700	700	700	700
5	Out	rent	850	850	850	850	850	850	850	850	850	850	850	850
6	Out	netfkix	10	10	10	10	10	10	10	10	10	10	10	10
7	Savings	premium bonds	50	50	50	50	50	50	50	50	50	50	50	50
8	Out	gym	35	35	35	35	35	35	35	35	35	35	35	35
9	Out	food	200	200	200	200	200	200	200	200	200	200	200	200
10	Out	a	150	150	150	150	150	150	150	150	150	150	150	150
11	Out	b	100	100	100	100	100	100	100	100	100	100	100	100
12	Out	child minder	350	350	350	350	350	350	350	350	350	350	350	350
13	In	Council Tax	270			270	270	270	270	270	270	270	270	270
14	Savings	Holiday	75	75	75	75	75	75	75	75	75	75	75	75
15	Savings	Emergencies	50	50	50	50	50	50	50	50	50	50	50	50
16	Savings	Christmas 2023	50	50	50	50	50	50	50	50	50	50	50	50
17	Savings	Clothing	30	30	30	30	30	30	30	30	30	30	30	30
In - Out

Cells with Data Validation
Cell	Allow	Criteria
A3:A17	List	=DATA!$D$2:$D$5

and then I have this on another tab

EXPENSES.xlsx
	B	C
2	Choose the Month	Jan - 2023
3
4	Total Estimated Income	0
5
6	Estimated Income
7	Description	Amount
8	#NUM!
9	#NUM!
10	#NUM!
11	#NUM!
12	#NUM!
13	#NUM!
14	#NUM!
15	#NUM!
16	#NUM!
17	#NUM!
18	#NUM!
19	#NUM!
20	#NUM!
21
22
23
24
25
26
27
Monthly Budget

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8	B8	=INDEX('In - Out'!$B$3:$B$20, SMALL(IF("in"='In - Out'!$A$3:$A$20, ROW('In - Out'!$A$3:$A$20)-ROW($A$1)+1), ROW('In - Out'!2:2)))
B9:B20	B9	=INDEX('In - Out'!$B$3:$B$20, SMALL(IF("in"='In - Out'!$A$3:$A$20, ROW('In - Out'!$A$3:$A$20)-ROW($A$1)+1), ROW(2:2)))
B22	B22	=IFERROR(INDEX(#REF!B:B,SMALL(IF(ISNUMBER(SEARCH("*",#REF!B:B)),ROW(#REF!B:B)),ROW(15:15))),"")
B23:B25	B23	=IFERROR(INDEX(#REF!B18:B35,SMALL(IF(ISNUMBER(SEARCH("*",#REF!B18:B35)),ROW(#REF!B18:B35)),ROW(16:16))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2:D2	List	=DATA!$A$2:$A$13

 

[AhoyNC]

Maybe something like this:

Sheet1

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	LIST ALL OF YOUR EXPENSES HERE
2	In / Out	Description	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
3	In	Wages	2000	2000	2000	2000	2200	2200	2200	2200	2200	2200	2200	2200
4	In	wages 2	1500	1500	700	700	700	700	700	700	700	700	700	700
5	Out	rent	850	850	850	850	850	850	850	850	850	850	850	850
6	Out	netfkix	10	10	10	10	10	10	10	10	10	10	10	10
7	Savings	premium bonds	50	50	50	50	50	50	50	50	50	50	50	50
8	Out	gym	35	35	35	35	35	35	35	35	35	35	35	35
9	Out	food	200	200	200	200	200	200	200	200	200	200	200	200
10	Out	a	150	150	150	150	150	150	150	150	150	150	150	150
11	Out	b	100	100	100	100	100	100	100	100	100	100	100	100
12	Out	child minder	350	350	350	350	350	350	350	350	350	350	350	350
13	In	Council Tax	270			270	270	270	270	270	270	270	270	270
14	Savings	Holiday	75	75	75	75	75	75	75	75	75	75	75	75
15	Savings	Emergencies	50	50	50	50	50	50	50	50	50	50	50	50
16	Savings	Christmas 2023	50	50	50	50	50	50	50	50	50	50	50	50
17	Savings	Clothing	30	30	30	30	30	30	30	30	30	30	30	30
Sheet1

Sheet2

Book2
	A	B	C
1
2		Choose the Month	Jan-23
3
4		Total Estimated Income	3770
5
6		Estimated Income
7		Description	Amount
8		Wages	2000
9		wages 2	1500
10		Council Tax	270
11
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8:B11	B8	=IFERROR(INDEX(Sheet1!$B$3:$B$17,AGGREGATE(15,6,(ROW(Sheet1!$A$3:$A$17)-ROW(Sheet1!$A$3)+1)/(Sheet1!$A$3:$A$17="In"),ROWS($B$8:B8))),"")
C8:C11	C8	=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!B8,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))

 

[shadbolt]

Maybe something like this:

Sheet1

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	LIST ALL OF YOUR EXPENSES HERE
2	In / Out	Description	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
3	In	Wages	2000	2000	2000	2000	2200	2200	2200	2200	2200	2200	2200	2200
4	In	wages 2	1500	1500	700	700	700	700	700	700	700	700	700	700
5	Out	rent	850	850	850	850	850	850	850	850	850	850	850	850
6	Out	netfkix	10	10	10	10	10	10	10	10	10	10	10	10
7	Savings	premium bonds	50	50	50	50	50	50	50	50	50	50	50	50
8	Out	gym	35	35	35	35	35	35	35	35	35	35	35	35
9	Out	food	200	200	200	200	200	200	200	200	200	200	200	200
10	Out	a	150	150	150	150	150	150	150	150	150	150	150	150
11	Out	b	100	100	100	100	100	100	100	100	100	100	100	100
12	Out	child minder	350	350	350	350	350	350	350	350	350	350	350	350
13	In	Council Tax	270			270	270	270	270	270	270	270	270	270
14	Savings	Holiday	75	75	75	75	75	75	75	75	75	75	75	75
15	Savings	Emergencies	50	50	50	50	50	50	50	50	50	50	50	50
16	Savings	Christmas 2023	50	50	50	50	50	50	50	50	50	50	50	50
17	Savings	Clothing	30	30	30	30	30	30	30	30	30	30	30	30
Sheet1

Sheet2

Book2
	A	B	C
1
2		Choose the Month	Jan-23
3
4		Total Estimated Income	3770
5
6		Estimated Income
7		Description	Amount
8		Wages	2000
9		wages 2	1500
10		Council Tax	270
11
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8:B11	B8	=IFERROR(INDEX(Sheet1!$B$3:$B$17,AGGREGATE(15,6,(ROW(Sheet1!$A$3:$A$17)-ROW(Sheet1!$A$3)+1)/(Sheet1!$A$3:$A$17="In"),ROWS($B$8:B8))),"")
C8:C11	C8	=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!B8,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))

That looks like its working for you but I don't have any results? am I doing something wrong?

 

[AhoyNC]

Are you getting an error, just blank cells or what?
One thing I noticed when I copied your data was a space after the word In. I removed the space thinking it was an error. Check your data and if there is a space remove it are change the formula to include the space "In ".
Also, I assumed all your dates were actual dates and not text. If some are text and some dates you will get an error.

 

[shadbolt]

I've removed the space and now it's working.

Only small issue is for Feb it displays council tax at 0 cost but I don't want to display any 0.

So if dividends are an in but only 1 month a year the rest of the year it won't be displayed?