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

 

[AhoyNC]

Right now I can't think of a way to not show the 0. I'll look at it later to see if I can think of something.

 

[awoohaw]

Right now I can't think of a way to not show the 0. I'll look at it later to see if I can think of something.

It could be a performance drain, but wraping it in an IFS(and( the calculation = 0, xxx ="Dividend"),"", the calculation)

 

[AhoyNC]

Try this:
Sheet 1

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	D
1
2		Choose the Month	Feb-23
3
4		Total Estimated Income	3500
5
6		Estimated Income
7		Description	Amount
8		Wages	2000
9		wages 2	1500
10
11
12
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8:B12	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")*(INDEX(Sheet1!$C$3:$N$17,0,MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0))>0)),ROWS($L$7:L7))),"")
C8:C11	C8	=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!L7,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))
C12	C12	=IF(B12="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!B12,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))

 

[shadbolt]

Try this:
Sheet 1

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	D
1
2		Choose the Month	Feb-23
3
4		Total Estimated Income	3500
5
6		Estimated Income
7		Description	Amount
8		Wages	2000
9		wages 2	1500
10
11
12
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8:B12	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")*(INDEX(Sheet1!$C$3:$N$17,0,MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0))>0)),ROWS($L$7:L7))),"")
C8:C11	C8	=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!L7,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))
C12	C12	=IF(B12="","",INDEX(Sheet1!$C$3:$N$17,MATCH(Sheet2!B12,Sheet1!$B$3:$B$17,0),MATCH(Sheet2!$C$2,Sheet1!$C$2:$N$2,0)))

c4 seems to work. for c8 i get #n/a ?

 

[AhoyNC]

Are you talking about Shee2?
The total in C4 is right, but you have an #N/A in for Wages in C8? Is C9 right?

 

[shadbolt]

sheet2 the type of expense comes through in column b but column c is not bringing in the values

 

[shadbolt]

I suppose I could just do a look up for the values

 

[AhoyNC]

Looks like I might have copied over some bad cell references.
Try this formula in C8 and drag down.

=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH($B8,Sheet1!$B$3:$B$17,0),MATCH($C$2,Sheet1!$C$2:$N$2,0)))

 

[AhoyNC]

I noticed a change that needs to be made to the formula in B8.

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	Feb-23
3
4		Total Estimated Income	3500
5
6		Estimated Income
7		Description	Amount
8		Wages	2000
9		wages 2	1500
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM(C8:C27)
B8:B9	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")*(INDEX(Sheet1!$C$3:$N$17,0,MATCH($C$2,Sheet1!$C$2:$N$2,0))>0)),ROWS($B$8:B8))),"")
C8:C9	C8	=IF($B8="","",INDEX(Sheet1!$C$3:$N$17,MATCH($B8,Sheet1!$B$3:$B$17,0),MATCH($C$2,Sheet1!$C$2:$N$2,0)))

 

[shadbolt]

That works great, thank you