Filter Formula Issues

[Pestomania]

Hello!!

I have this formula which works perfect to show ALL items, but I want to filter by location and Risk Category options, but cannot get it to work.

This one works perfect to show ALL records, regardless of location or category.

=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=Sheet1!D$6)*(Risk_Table[Cost Impact]=Sheet1!$B1)*(Risk_Table[Location]=$K$1)*(Risk_Table[Risk Category]=$K$2),""))

This one works if there is a field in location and category, but if location or category, it returns a #Value.

=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=Sheet1!C$6)*(Risk_Table[Cost Impact]=Sheet1!$B1)*IF($K$1<>"",(Risk_Table[Location]=$K$1),"")*IF($K$2<>"",(Risk_Table[Risk Category]=$K$2),""),""))

I want to ensure that if either field are null, it results in no filtering for that field.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Likelihood	Extreme (>$10M)								Site Location	Dallas, TX
2		High (>$5M)			1					Risk Category	Strategic - Technology
3		Moderate (>$2M)
4		Slightly (<$2M)
5		Negligible
6			Extreme (>90%)	High (>75%)	Moderate (>50%)	Low (>10%)	Negligible (<10%)
7		Likelihood
Sheet1

Cell Formulas
Range	Formula
C1	C1	=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=Sheet1!C$6)*(Risk_Table[Cost Impact]=Sheet1!$B1)*IF($K$1<>"",(Risk_Table[Location]=$K$1),"")*IF($K$2<>"",(Risk_Table[Risk Category]=$K$2),""),""))
D1:G1,C2:G5	D1	=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=Sheet1!D$6)*(Risk_Table[Cost Impact]=Sheet1!$B1)*(Risk_Table[Location]=$K$1)*(Risk_Table[Risk Category]=$K$2),""))

Named Ranges
Name	Refers To	Cells
Cost_Impact	=Sheet1!$B$1:$B$5	C1:G1
Likelihood	=Sheet1!$C$6:$G$6	C1:C5

Cells with Data Validation
Cell	Allow	Criteria
K1	List	=Locations
K2	List	=Risk_Category

 

[Fluff]

You're welcome & thanks for the feedback.