XLOOKUP filtering for one look up value but not another.

[Queeza]

Hello
I have a column of job numbers that I need to filter by Project Manager. The Project Manager is selected from a drop-down list.
XLOOKUP is filtering for EP but returns all values for SM.

Any help with this would be appreciated.

The formular I'm using is:
Cell H3: =IF(XLOOKUP($K$1,$A$3:$A$34,B3:B34,"",0,1)=0,"",(XLOOKUP($K$1,$A$3:$A$34,B3:B34,"",0,1)))
Cell H4: =IF(XLOOKUP($K$1,$A$3:$A$34,B4:B35,"",0,1)=0,"",(XLOOKUP($K$1,$A$3:$A$34,B4:B35,"",0,1)))
Cell H5: =IF(XLOOKUP($K$1,$A$3:$A$34,B5:B36,"",0,1)=0,"",(XLOOKUP($K$1,$A$3:$A$34,B5:B36,"",0,1)))
.

 

[SunnyAlv]

why don't you use the Filter formula for simplicity

=FILTER($B$3:$B$34,A3:A34=K1,"")

 

[StephenCrump]

Can't you use a single array formula:

=FILTER(B3:B34,A3:A34=K1,"n/a")

 

[Queeza]

I finally worked out how to get the mini sheet in. For some reason I can't select D1 to activate the drop-down box.
The filter suggestions, thank you, I couldn't get to work.
Hopefully the mini sheet will better explain the situation. This is just a small part of a large workbook producing graphs and visual representation of expenditure.
Again, any help would be appreciated.

Xlookup problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	NORTH	2024-2025	Project Manager	SM			Program Manager	EP	HIDE	HIDE	HIDE
2						2024-2025 Expenditure for NORTH District		SM	Process Function	Project Description	Project Manager	Work Order Description	Work Order
3	District	Work Order	Work Order Description	Project Description	Lookup Title	Graph Title	Process Function		Line Marking	Line Marking - North	SM	ANNUAL: CNTL PARKING SCHEMES - Lines and	M000064
4	NORTH	M000064	ANNUAL: CNTL PARKING SCHEMES - Lines and	Line Marking - North	M000064 - ANNUAL: CNTL PARKING SCHEMES - Lines and	2024-2025 Expenditure for M000064 - ANNUAL: CNTL PARKING SCHEMES - Lines and	Line Marking		Line Marking	Line Marking - North	SM	ANNUAL: NORTH PARKING SCHEMES - Lines an	M000065
5	NORTH	M000065	ANNUAL: NORTH PARKING SCHEMES - Lines an	Line Marking - North	M000065 - ANNUAL: NORTH PARKING SCHEMES - Lines an	2024-2025 Expenditure for M000065 - ANNUAL: NORTH PARKING SCHEMES - Lines an	Line Marking		Bridge Infrastructure	Foot Bridge Maintenance - North	SM	Foot Bridge Maintenance	M000097
6	NORTH	M000097	Foot Bridge Maintenance	Foot Bridge Maintenance - North	M000097 - Foot Bridge Maintenance	2024-2025 Expenditure for M000097 - Foot Bridge Maintenance	Bridge Infrastructure		Footpaths & Cycleways	Formed Path Maintenance - North	SM	Formed Path Maintenance	M000098
7	NORTH	M000098	Formed Path Maintenance	Formed Path Maintenance - North	M000098 - Formed Path Maintenance	2024-2025 Expenditure for M000098 - Formed Path Maintenance	Footpaths & Cycleways		Footpaths & Cycleways	Formed Path Cleaning - North	SM	Formed Path Cleaning	M000099
8	NORTH	M000099	Formed Path Cleaning	Formed Path Cleaning - North	M000099 - Formed Path Cleaning	2024-2025 Expenditure for M000099 - Formed Path Cleaning	Footpaths & Cycleways		Footpaths & Cycleways	Formed Path Cleaning - North	SM	Formed Path Cleaning (CBD)	M000100
9	NORTH	M000100	Formed Path Cleaning (CBD)	Formed Path Cleaning - North	M000100 - Formed Path Cleaning (CBD)	2024-2025 Expenditure for M000100 - Formed Path Cleaning (CBD)	Footpaths & Cycleways		Footpaths & Cycleways	Path Sweeping (High Profile) - North	SM	Path Sweeping (High Profile)	M000101
10	NORTH	M000101	Path Sweeping (High Profile)	Path Sweeping (High Profile) - North	M000101 - Path Sweeping (High Profile)	2024-2025 Expenditure for M000101 - Path Sweeping (High Profile)	Footpaths & Cycleways		Footpaths & Cycleways	Path Sweeping (Suburban) - North	SM	Path Sweeping (Suburban)	M000102
11	NORTH	M000102	Path Sweeping (Suburban)	Path Sweeping (Suburban) - North	M000102 - Path Sweeping (Suburban)	2024-2025 Expenditure for M000102 - Path Sweeping (Suburban)	Footpaths & Cycleways		High Profile	High Profile Area Maint (CBD) - North	SM	High Profile Area Maintenance (CBD)	M000103
12	NORTH	M000103	High Profile Area Maintenance (CBD)	High Profile Area Maint (CBD) - North	M000103 - High Profile Area Maintenance (CBD)	2024-2025 Expenditure for M000103 - High Profile Area Maintenance (CBD)	High Profile		Kerb & Channel	Kerb & Channel Maintenance - North	SM	Kerb & Channel Maintenance	M000104
13	NORTH	M000104	Kerb & Channel Maintenance	Kerb & Channel Maintenance - North	M000104 - Kerb & Channel Maintenance	2024-2025 Expenditure for M000104 - Kerb & Channel Maintenance	Kerb & Channel		Road Infrastructure	Unsealed Road Maintenance - North	SM	Unsealed Road Maintenance	M000105
14	NORTH	M000105	Unsealed Road Maintenance	Unsealed Road Maintenance - North	M000105 - Unsealed Road Maintenance	2024-2025 Expenditure for M000105 - Unsealed Road Maintenance	Road Infrastructure		Road Infrastructure	Unsealed Shoulder Maintenance - North	SM	Unsealed Shoulder Maintenance	M000106
15	NORTH	M000106	Unsealed Shoulder Maintenance	Unsealed Shoulder Maintenance - North	M000106 - Unsealed Shoulder Maintenance	2024-2025 Expenditure for M000106 - Unsealed Shoulder Maintenance	Road Infrastructure		Road Infrastructure	Pothole Repairs - North	SM	Pothole Repairs	M000107
16	NORTH	M000107	Pothole Repairs	Pothole Repairs - North	M000107 - Pothole Repairs	2024-2025 Expenditure for M000107 - Pothole Repairs	Road Infrastructure		Road Infrastructure	Edge Break Repairs - North	SM	Edge Break Repairs	M000108
17	NORTH	M000108	Edge Break Repairs	Edge Break Repairs - North	M000108 - Edge Break Repairs	2024-2025 Expenditure for M000108 - Edge Break Repairs	Road Infrastructure		Road Infrastructure	Minor Reseals - North	SM	Minor Reseals	M000109
18	NORTH	M000109	Minor Reseals	Minor Reseals - North	M000109 - Minor Reseals	2024-2025 Expenditure for M000109 - Minor Reseals	Road Infrastructure		Street Sweeping	Street Sweeping - North	SM	Street Sweeping	M000110
19	NORTH	M000110	Street Sweeping	Street Sweeping - North	M000110 - Street Sweeping	2024-2025 Expenditure for M000110 - Street Sweeping	Street Sweeping		Street Sweeping	Street Sweeping - North	SM	Street Sweeping (CBD)	M000111
20	NORTH	M000111	Street Sweeping (CBD)	Street Sweeping - North	M000111 - Street Sweeping (CBD)	2024-2025 Expenditure for M000111 - Street Sweeping (CBD)	Street Sweeping		Line Marking	Line Marking - South	EP	ANNUAL: SOUTH PARKING SCHEMES - Lines an	M000066
21	NORTH	M000066	ANNUAL: SOUTH PARKING SCHEMES - Lines an	Line Marking - South	M000066 - ANNUAL: SOUTH PARKING SCHEMES - Lines an	2024-2025 Expenditure for M000066 - ANNUAL: SOUTH PARKING SCHEMES - Lines an	Line Marking		Bridge Infrastructure	Foot Bridge Maintenance - South	EP	Foot Bridge Maintenance	M000233
22	NORTH	M000233	Foot Bridge Maintenance	Foot Bridge Maintenance - South	M000233 - Foot Bridge Maintenance	2024-2025 Expenditure for M000233 - Foot Bridge Maintenance	Bridge Infrastructure		Bridge Infrastructure	Road Bridge Maintenance	EP	Road Bridge Maintenance	M000234
23	NORTH	M000234	Road Bridge Maintenance	Road Bridge Maintenance	M000234 - Road Bridge Maintenance	2024-2025 Expenditure for M000234 - Road Bridge Maintenance	Bridge Infrastructure		Footpaths & Cycleways	Unformed Path Maintenance - South	EP	Unformed Path Maintenance	M000235
24	NORTH	M000235	Unformed Path Maintenance	Unformed Path Maintenance - South	M000235 - Unformed Path Maintenance	2024-2025 Expenditure for M000235 - Unformed Path Maintenance	Footpaths & Cycleways		Footpaths & Cycleways	Formed Path Maintenance - South	EP	Formed Path Maintenance	M000236
25	NORTH	M000236	Formed Path Maintenance	Formed Path Maintenance - South	M000236 - Formed Path Maintenance	2024-2025 Expenditure for M000236 - Formed Path Maintenance	Footpaths & Cycleways		Footpaths & Cycleways	Formed Path Cleaning - South	EP	Formed Path Cleaning	M000237
26	NORTH	M000237	Formed Path Cleaning	Formed Path Cleaning - South	M000237 - Formed Path Cleaning	2024-2025 Expenditure for M000237 - Formed Path Cleaning	Footpaths & Cycleways		Footpaths & Cycleways	Path Sweeping (Suburban) - South	EP	Path Sweeping (Suburban)	M000238
27	NORTH	M000238	Path Sweeping (Suburban)	Path Sweeping (Suburban) - South	M000238 - Path Sweeping (Suburban)	2024-2025 Expenditure for M000238 - Path Sweeping (Suburban)	Footpaths & Cycleways		Kerb & Channel	Kerb & Channel Maintenance - South	EP	Kerb & Channel Maintenance	M000239
28	NORTH	M000239	Kerb & Channel Maintenance	Kerb & Channel Maintenance - South	M000239 - Kerb & Channel Maintenance	2024-2025 Expenditure for M000239 - Kerb & Channel Maintenance	Kerb & Channel		Road Infrastructure	Unsealed Road Maintenance - South	EP	Unsealed Road Maintenance	M000240
29	NORTH	M000240	Unsealed Road Maintenance	Unsealed Road Maintenance - South	M000240 - Unsealed Road Maintenance	2024-2025 Expenditure for M000240 - Unsealed Road Maintenance	Road Infrastructure		Road Infrastructure	Unsealed Shoulder Maintenance - South	EP	Unsealed Shoulder Maintenance	M000241
30	NORTH	M000241	Unsealed Shoulder Maintenance	Unsealed Shoulder Maintenance - South	M000241 - Unsealed Shoulder Maintenance	2024-2025 Expenditure for M000241 - Unsealed Shoulder Maintenance	Road Infrastructure		Road Infrastructure	Pothole Repairs - South	EP	Pothole Repairs	M000242
31	NORTH	M000242	Pothole Repairs	Pothole Repairs - South	M000242 - Pothole Repairs	2024-2025 Expenditure for M000242 - Pothole Repairs	Road Infrastructure		Road Infrastructure	Edge Break Repairs - South	EP	Edge Break Repairs	M000243
32	NORTH	M000243	Edge Break Repairs	Edge Break Repairs - South	M000243 - Edge Break Repairs	2024-2025 Expenditure for M000243 - Edge Break Repairs	Road Infrastructure		Road Infrastructure	Minor Reseals - South	EP	Minor Reseals	M000244
33	NORTH	M000244	Minor Reseals	Minor Reseals - South	M000244 - Minor Reseals	2024-2025 Expenditure for M000244 - Minor Reseals	Road Infrastructure		Street Sweeping	Street Sweeping - South	EP	Street Sweeping	M000245
Titles

Cell Formulas
Range	Formula
A1	A1	=IFS(D1="EP","SOUTH",D1="SM","NORTH")
F2	F2	=CONCATENATE(B1," ","Expenditure for"," ",A1," ","District")
A4:A33	A4	=IF(B4="","",$A$1)
B4:B33	B4	=IF(XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!M3:M33,"",0,1)=0,"",(XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!M3:M33,"",0,1)))
C4:C33	C4	=IF(XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!$L3:$L33,"",0,1)=0,"",XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!$L3:$L33,"",0,1))
D4:D33	D4	=IF(XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!J3:J33,"",0,1)=0,"",XLOOKUP($D$1,Titles!$K$3:$K$33,Titles!$J3:$J33,"",0,1))
E4:E33	E4	=IF(B4="","",CONCATENATE(B4," - ",C4))
F4:F33	F4	=IF(B4="","",CONCATENATE($B$1," Expenditure for ",B4," - ",C4))
G4:G33	G4	=IF(XLOOKUP($D$1,$K$3:$K$33,I3:I33,"",0,1)=0,"",(XLOOKUP($D$1,$K$3:$K$33,I3:I33,"",0,1)))

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=$H$1:$H$2

 

[StephenCrump]

You haven't shown us your Titles worksheet.

But based on the formulae you're using, and the fact that our Filter suggestion isn't working for you, I am guessing that your Project Manager column is not fully populated?

	A	B	C	D	E	F
1		Project Description	Project Manager	Work Order Description	Work Order
2
3		Blah	XX	Blah	Blah
4		Blah		Blah	Blah
5		Line Marking - North	SM	ANNUAL: CNTL PARKING SCHEMES - Lines and	M000064
6		Line Marking - North		ANNUAL: NORTH PARKING SCHEMES - Lines an	M000065
7		Foot Bridge Maintenance - North		Foot Bridge Maintenance	M000097
8		Formed Path Maintenance - North		Formed Path Maintenance	M000098
9		Formed Path Cleaning - North		Formed Path Cleaning	M000099
10		Blah	YY	Blah	Blah
11		Blah		Blah	Blah
12		Blah		Blah	Blah
13		Blah	ZZ	Blah	Blah
14		Blah		Blah	Blah
15
16		Doesn't work
17		Project Description
18	SM	Line Marking - North
19
20		Does work
21	SM	Line Marking - North
22		Line Marking - North
23		Foot Bridge Maintenance - North
24		Formed Path Maintenance - North
25		Formed Path Cleaning - North
26
Titles

Cell Formulas
Range	Formula
B18	B18	=FILTER(B3:B14,C3:C14=A18)
B21:B25	B21	=FILTER(B3:B14,SCAN("",C3:C14,LAMBDA(a,b,IF(b="",a,b)))=A21,"n/a")
Dynamic array formulas.

 

[Queeza]

why don't you use the Filter formula for simplicity

=FILTER($B$3:$B$34,A3:A34=K1,"")

Thaks SonyAlv - I couldn't get this to work for some reason. Due to the report I use for this now being changed, I'm going to go in whole different direction. Thanks for you time.

 

[Queeza]

Can't you use a single array formula:

=FILTER(B3:B34,A3:A34=K1,"n/a")

Thaks Stephen. My whole system has now been changed and I'm going to have to rethink the whole thing. Thanks for your response.

 

[Queeza]

You haven't shown us your Titles worksheet.

But based on the formulae you're using, and the fact that our Filter suggestion isn't working for you, I am guessing that your Project Manager column is not fully populated?

	A	B	C	D	E	F
1		Project Description	Project Manager	Work Order Description	Work Order
2
3		Blah	XX	Blah	Blah
4		Blah		Blah	Blah
5		Line Marking - North	SM	ANNUAL: CNTL PARKING SCHEMES - Lines and	M000064
6		Line Marking - North		ANNUAL: NORTH PARKING SCHEMES - Lines an	M000065
7		Foot Bridge Maintenance - North		Foot Bridge Maintenance	M000097
8		Formed Path Maintenance - North		Formed Path Maintenance	M000098
9		Formed Path Cleaning - North		Formed Path Cleaning	M000099
10		Blah	YY	Blah	Blah
11		Blah		Blah	Blah
12		Blah		Blah	Blah
13		Blah	ZZ	Blah	Blah
14		Blah		Blah	Blah
15
16		Doesn't work
17		Project Description
18	SM	Line Marking - North
19
20		Does work
21	SM	Line Marking - North
22		Line Marking - North
23		Foot Bridge Maintenance - North
24		Formed Path Maintenance - North
25		Formed Path Cleaning - North
26
Titles

Cell Formulas
Range	Formula
B18	B18	=FILTER(B3:B14,C3:C14=A18)
B21:B25	B21	=FILTER(B3:B14,SCAN("",C3:C14,LAMBDA(a,b,IF(b="",a,b)))=A21,"n/a")
Dynamic array formulas.

Just started a whole new spreadsheet and this is working. Thanks so much.

 

[StephenCrump]

Thanks for posting back. I'm glad we could help.