Xlookup or Alternative?

[Thomas Scott]

I am trying to select and list reduced budgets for units on a whatif situation in an imaginary 8 quarters. The names are listed and when possible future funds would be available but want to see a list of the units that would receive no funding depending on the number of funds allocated. I looked at Xlookup as a possible solution but the approach would have to search through all 8 to see if the cut criteria had an impact in creating non-funded units. I also show below the same data but sorted which is not as desirable but I could live with it. The keys are the blue column (cuts) and the red column resulting unit names. Any ideas on how to approach this would be appreciated. Thanks very much.

Budget Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Quarterly (thousand)
2	Dept	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	TotalFunded	PlannedCuts	NonFundDepts
3	Alpha	1				1				2	0
4	Beta		1			1				2	1
5	Gamma		1			1		1		3	2	Alpha, Beta
6	Delta	1		1	1		1			4	3	Gamma, Eta
7	Epsilon	1				1	1	1	1	5	4	Delta, Zeta
8	Zeta	1		1			1	1		4	5	Epsilon
9	Eta	1		1		1				3	6	Theta
10	Theta	1		1	1	1		1	1	6
11
12
13	Dept	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	TotalFunded	PlannedCuts	NonFundDepts
14	Alpha	1				1				2	0
15	Beta		1			1				2	1
16	Gamma		1			1		1		3	2	Alpha, Beta
17	Eta	1		1		1				3	3	Gamma, Eta
18	Delta	1		1	1		1			4	4	Delta, Zeta
19	Zeta	1		1			1	1		4	5	Epsilon
20	Epsilon	1				1	1	1	1	5	6	Theta
21	Theta	1		1	1	1		1	1	6
Budgets

Cell Formulas
Range	Formula
J3:J10,J14:J21	J3	=SUM(A3:I3)

 

[Phuoc]

Maybe like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Quarterly (thousand)
2	Dept	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	TotalFunded	PlannedCuts	NonFundDepts
3	Alpha	1				1				2	0
4	Beta		1			1				2	1
5	Gamma		1			1		1		3	2	Alpha, Beta
6	Delta	1		1	1		1			4	3	Gamma, Eta
7	Epsilon	1				1	1	1	1	5	4	Delta, Zeta
8	Zeta	1		1			1	1		4	5	Epsilon
9	Eta	1		1		1				3	6	Theta
10	Theta	1		1	1	1		1	1	6
Sheet1

Cell Formulas
Range	Formula
L3:L9	L3	=TEXTJOIN(", ",1,FILTER($A$3:$A$10,$J$3:$J$10=K3,""))
J3:J10	J3	=SUM(A3:I3)

 

[Peter_SSs]

@Thomas Scott
Are you still using Excel 2019 as shown in your profile?

 

[Thomas Scott]

No, now using Excel 2021.

 

[Peter_SSs]

No, now using Excel 2021.

Thanks for responding and updating your profile.

Any response to the suggestion in post #2?

 

[Thomas Scott]

Problem solved. Excellent solution. Did not know about these functions and their possible use.

 

[Thomas Scott]

Ok. I thought I was done, but sometimes there are many units that would be listed and I thought to add a column for the number cut by simply counting the names in the last column. My solution almost works except for when there are no cut labels which should result in a blank cell but my formula as an addition. I've tried some IF statements but after a couple of hours gave up. Anyway here it is and would not be surprised if it is an easy answer. Thanks again.

Budget Sample II.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Quarterly (thousand)
2	Dept	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	TotalFunded	PlannedCuts	NumberCut	NonFundDepts
3	Beta		1			1				2	1	1
4	Gamma		1			1		1		3	2	1	Beta
5	Gamma2		1			1			1	3	3	3	Gamma, Gamma2, Eta
6	Delta	1		1	1		1			4	4	2	Delta, Zeta
7	Epsilon	1				1	1	1	1	5	5	1	Epsilon
8	Zeta	1		1			1	1		4	6	1	Theta
9	Eta	1		1		1				3
10	Theta	1		1	1	1		1	1	6
Budgets2

Cell Formulas
Range	Formula
L3:L8	L3	=LEN(TRIM(M3))-LEN(SUBSTITUTE(M3," ",""))+1
M3:M9	M3	=TEXTJOIN(", ",1,FILTER($A$3:$A$10,$J$3:$J$10=K3,""))

 

[Peter_SSs]

Just deal with that case separately like this?

24 12 11.xlsm
	L	M
1
2	NumberCut	NonFundDepts
3
4	1	Beta
5	3	Gamma, Gamma2, Eta
6	2	Delta, Zeta
7	1	Epsilon
8	1	Theta
9
10
Sheet3

Cell Formulas
Range	Formula
M3:M9	M3	=TEXTJOIN(", ",1,FILTER($A$3:$A$10,$J$3:$J$10=K3,""))
L3:L10	L3	=IF(M3="","",LEN(M3)-LEN(SUBSTITUTE(M3,",",""))+1)