Filter Formula Issues

P

Pestomania

Active Member
Joined
May 30, 2018
Messages
332
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula: 
=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.
Excel Formula: 
=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
ABCDEFGHIJK
1LikelihoodExtreme (>$10M)     Site LocationDallas, TX
2High (>$5M)  1  Risk CategoryStrategic - Technology
3Moderate (>$2M)     
4Slightly (<$2M)     
5Negligible     
6Extreme (>90%)High (>75%)Moderate (>50%)Low (>10%)Negligible (<10%)
7Likelihood
Sheet1
Cell Formulas
RangeFormula
C1C1=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:G5D1=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
NameRefers ToCells
Cost_Impact=Sheet1!$B$1:$B$5C1:G1
Likelihood=Sheet1!$C$6:$G$6C1:C5
Cells with Data Validation
CellAllowCriteria
K1List=Locations
K2List=Risk_Category
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
How about
Excel Formula: 
=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=D$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2)),""))
 
Upvote 0
Solution
Fluff said:
How about
Excel Formula: 
=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=D$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2)),""))
Click to expand...
Hi @Fluff,

When I inputted that, it just returned no values at all. I have included two screens below:

Risk Cube for Risk Register.xlsx
ABCDEFGHIJK
1LikelihoodExtreme (>$10M)     Site LocationDallas, TX
2High (>$5M)     Risk CategoryStrategic - Technology
3Moderate (>$2M)     
4Slightly (<$2M)     
5Negligible     
6Extreme (>90%)High (>75%)Moderate (>50%)Low (>10%)Negligible (<10%)
7Likelihood
Sheet1
Cell Formulas
RangeFormula
C1:C5C1=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2)),""))
D1:D5D1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk ID],(Risk_Table[Risk Rating]=D$6)*(Risk_Table[Likelihood]=$B1)*(IF($K$1="",1,Risk_Table[SME Monitoring Schedule]=$K$1))*(IF($K$2="",1,Risk_Table[Date Legal Dept Reviewed]=$K$2)),""))
E1:E5E1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk Assessment Date],(Risk_Table[Remediation Target Date]=E$6)*(Risk_Table[Risk Rating]=$B1)*(IF($K$1="",1,Risk_Table[Risk Category]=$K$1))*(IF($K$2="",1,Risk_Table[Legal Review Required?]=$K$2)),""))
F1:F5F1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk Name (Scenario)],(Risk_Table[Risk Assessment Status]=F$6)*(Risk_Table[Remediation Target Date]=$B1)*(IF($K$1="",1,Risk_Table[Date Legal Dept Reviewed]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Description]=$K$2)),""))
G1:G5G1=TEXTJOIN(", ",,FILTER(Risk_Table[Location],(Risk_Table[BOD Risk Assessment Status]=G$6)*(Risk_Table[Risk Assessment Status]=$B1)*(IF($K$1="",1,Risk_Table[Legal Review Required?]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Owner]=$K$2)),""))
Named Ranges
NameRefers ToCells
Cost_Impact=Sheet1!$B$1:$B$5C1:G1
Likelihood=Sheet1!$C$6:$G$6C1:C5
Cells with Data Validation
CellAllowCriteria
K1List=Locations
K2List=Risk_Category



If I remove the location and category, I receive this:

Risk Cube for Risk Register.xlsx
ABCDEFGHIJK
1LikelihoodExtreme (>$10M)     Site Location
2High (>$5M)     Risk Category
3Moderate (>$2M)     
4Slightly (<$2M)     
5Negligible41, 42    
6Extreme (>90%)High (>75%)Moderate (>50%)Low (>10%)Negligible (<10%)
7Likelihood
Sheet1
Cell Formulas
RangeFormula
C1:C5C1=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2)),""))
D1:D5D1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk ID],(Risk_Table[Risk Rating]=D$6)*(Risk_Table[Likelihood]=$B1)*(IF($K$1="",1,Risk_Table[SME Monitoring Schedule]=$K$1))*(IF($K$2="",1,Risk_Table[Date Legal Dept Reviewed]=$K$2)),""))
E1:E5E1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk Assessment Date],(Risk_Table[Remediation Target Date]=E$6)*(Risk_Table[Risk Rating]=$B1)*(IF($K$1="",1,Risk_Table[Risk Category]=$K$1))*(IF($K$2="",1,Risk_Table[Legal Review Required?]=$K$2)),""))
F1:F5F1=TEXTJOIN(", ",,FILTER(Risk_Table[Risk Name (Scenario)],(Risk_Table[Risk Assessment Status]=F$6)*(Risk_Table[Remediation Target Date]=$B1)*(IF($K$1="",1,Risk_Table[Date Legal Dept Reviewed]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Description]=$K$2)),""))
G1:G5G1=TEXTJOIN(", ",,FILTER(Risk_Table[Location],(Risk_Table[BOD Risk Assessment Status]=G$6)*(Risk_Table[Risk Assessment Status]=$B1)*(IF($K$1="",1,Risk_Table[Legal Review Required?]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Owner]=$K$2)),""))
Named Ranges
NameRefers ToCells
Cost_Impact=Sheet1!$B$1:$B$5C1:G1
Likelihood=Sheet1!$C$6:$G$6C1:C5
Cells with Data Validation
CellAllowCriteria
K1List=Locations
K2List=Risk_Category
 
Upvote 0
I have attached a version of my table.

Index #Risk IDRisk Assessment DateRisk Name (Scenario)LocationSME Monitoring ScheduleRisk CategoryDate Legal Dept ReviewedLegal Review Required?Risk DescriptionRisk OwnerRisk SMEUnmitigated Impact ValueCurrent Impact ValueCost ImpactLikelihoodRisk RatingRemediation Target DateRisk Assessment StatusBOD Risk Assessment StatusDate Sent to Internal AuditAssigned ERM CoordinatorERM Staff / Coordinator NotesCreated DateCreated ByModifiedModified ByLatest CommentColumn19
114-00018/1/2016Data 1Dallas, TXOngoingStrategic - TechnologyData 1 DescriptionHigh (>$5M)Moderate (>50%)Accepted1/5/20238 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
214-00028/1/2016Data 2AnnualOperational - ProductionData 2 DescriptionExtreme (>$10M)Moderate (>50%)Accepted3/31/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
315-00028/1/2016Data 3Prestwick, ScotlandAnnualComplianceData 3 DescriptionExtreme (>$10M)Low (>10%)Accepted6/30/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
415-00048/2/2016Data 4Tulsa, OKAnnualOperational - ProductionData 4 DescriptionModerate (>$2M)Negligible (<10%)Accepted12/31/20178 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
515-00056/1/2015Data 5AnnualOperational - ProductionData 5 DescriptionSlightly (<$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
615-00077/28/2016Data 6AnnualOperational - ProductionData 6 DescriptionExtreme (>$10M)Low (>10%)Accepted12/31/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
715-00098/1/2016Data 7AnnualOperational - ProductionData 7 DescriptionExtreme (>$10M)Low (>10%)Accepted12/31/20158 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
815-00137/1/2016Data 8OngoingOperational - QualityData 8 DescriptionSlightly (<$2M)Negligible (<10%)Accepted9/30/20158 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
915-00147/1/2016Data 9AnnualOperational - ProductionData 9 DescriptionSlightly (<$2M)Negligible (<10%)Accepted1/30/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1015-00157/1/2016Data 10AnnualOperational - ProductionData 10 DescriptionSlightly (<$2M)Negligible (<10%)Accepted3/1/20168 - Risk Accepted4 - Risk Accepted9/29/20156/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1115-00218/16/2016Data 11AnnualOperational - ProductionData 11 DescriptionModerate (>$2M)Low (>10%)Accepted5/31/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1215-00008/16/2016Data 12AnnualOperational - ProductionData 12 DescriptionExtreme (>$10M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk AcceptedRemediated6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1315-00246/1/2015Data 13OngoingFinancial - Financial LiabilityData 13 DescriptionExtreme (>$10M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1415-00366/1/2015Data 14RealizedStrategic - IndustryData 14 DescriptionExtreme (>$10M)High (>75%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1515-00386/1/2015Data 15OngoingFinancial - ContractingData 15 DescriptionExtreme (>$10M)Moderate (>50%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1615-00396/1/2015Data 16OngoingFinancial - Financial LiabilityData 16 DescriptionExtreme (>$10M)Moderate (>50%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1715-00418/30/2016Data 17OngoingFinancial - Financial LiabilityData 17 DescriptionExtreme (>$10M)Low (>10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1815-00426/1/2015Data 18OngoingFinancial - ContractingData 18 DescriptionExtreme (>$10M)Low (>10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
1915-00436/1/2015Data 19OngoingFinancial - ContractingData 19 DescriptionExtreme (>$10M)Low (>10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2015-00446/1/2015Data 20AnnualOperational - ProductionData 20 DescriptionExtreme (>$10M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2115-00456/1/2015Data 21AnnualOperational - ProductionData 21 DescriptionExtreme (>$10M)Low (>10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2215-00476/1/2015Data 22AnnualOperational - QualityData 22 DescriptionExtreme (>$10M)Negligible (<10%)Accepted3/31/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2315-00486/1/2016Data 23OngoingComplianceData 23 DescriptionExtreme (>$10M)Moderate (>50%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2415-00506/1/2015Data 24AnnualOperational - ProductionData 24 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2515-00516/6/2016Data 25AnnualOperational - ProductionData 25 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2615-00546/1/2015Data 26AnnualOperational - ProductionData 26 DescriptionExtreme (>$10M)Negligible (<10%)Accepted3/31/20168 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2715-00566/1/2015Data 27OngoingStrategic - MarketData 27 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2815-00576/1/2015Data 28OngoingStrategic - MarketData 28 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
2916-00016/1/2016Data 29AnnualOperational - Information TechnologyData 29 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3016-00216/1/2016Data 30OngoingStrategic - IndustryData 30 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3116-00226/1/2016Data 31Operational - ProductionData 31 DescriptionExtreme (>$10M)Negligible (<10%)5 - Ready for ERM Council2 - Ready for ERM Council6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3216-00299/12/2016Data 32OngoingOperational - ProductionData 32 DescriptionModerate (>$2M)Moderate (>50%)Accepted8 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3316-00306/1/2016Data 33Operational - ProductionData 33 DescriptionModerate (>$2M)1 - Data Collection in Progress1 - Data Collection in Progress6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3416-0030-B6/1/2015Data 34OngoingFinancial - Capital StructureData 34 DescriptionModerate (>$2M)Moderate (>50%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3516-0030-C6/1/2015Data 35OngoingStrategic - Human CapitalData 35 DescriptionExtreme (>$10M)Moderate (>50%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3616-0030-D6/1/2015Data 36RealizedStrategic - IndustryData 36 DescriptionModerate (>$2M)Negligible (<10%)Accepted8 - Risk Accepted4 - Risk Accepted6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3716-00316/1/2016Data 37Operational - ProductionData 37 DescriptionNegligible5 - In Queue6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3821-00016/1/2021Data 38OngoingOperational - Information TechnologyData 38 DescriptionNegligibleLow (>10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
3921-00026/1/2021Data 39OngoingOperational - Information TechnologyData 39 DescriptionNegligibleNegligible (<10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4021-00036/1/2021Data 40OngoingOperational - Information TechnologyData 40 DescriptionNegligibleLow (>10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4120-00016/1/2020Data 41OngoingOperational - ProductionData 41 DescriptionNegligibleExtreme (>90%)1/5/20236/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4222-00016/1/2022Data 42OngoingOperational - ProductionData 42 DescriptionNegligibleExtreme (>90%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4322-00026/1/2022Data 43OngoingOperational - Information TechnologyData 43 DescriptionNegligibleNegligible (<10%)7a - Risk Remediation in Progress6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4421-00046/1/2021Data 44OngoingOperational - Information TechnologyData 44 DescriptionNegligibleNegligible (<10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4522-00036/1/2022Data 45OngoingOperational - ProductionData 45 DescriptionNegligibleLow (>10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4622-00046/1/2022Data 46OngoingOperational - ProductionData 46 DescriptionNegligibleNegligible (<10%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
4722-00056/1/2022Data 47OngoingOperational - Information TechnologyData 47 DescriptionNegligibleModerate (>50%)6/4/2024 13:02preston.warden@spiritaero.com6/5/2024 7:03preston.warden@spiritaero.com
 
Upvote 0
Your columns are different as when you dragged the formula across the column headers changes.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Hi @Fluff, I tried to make it one step better, but am not getting it to function:

Excel Formula: 
=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2))*(IF($K$3="",1,Risk_Table[Risk Category]=($K$3&"*"))),""))

I tried to get the formula to also set up "based on K3, return all values that start with K3 from the Risk_Category". I tried ($K$3&"*")), text("$K$3&"*", "abc") and so many other things and still can't get it to function.

I should receive numerous results, but am returning none.
Risk Cube for Risk Register.xlsx
ABCDEFGHIJK
1Cost ImpactExtreme (>$10M)     Site Location
2High (>$5M)     Risk Category
3Moderate (>$2M)     High Level Risk CategoryOperational
4Slightly (<$2M)     
5Negligible     
6Extreme (>90%)High (>75%)Moderate (>50%)Low (>10%)Negligible (<10%)
7Likelihood
Sheet1
Cell Formulas
RangeFormula
C1:G5C1=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2))*(IF($K$3="",1,Risk_Table[Risk Category]=TEXT($K$3&"*","abc"))),""))
Named Ranges
NameRefers ToCells
Cost_Impact=Sheet1!$B$1:$B$5C1:G1
Likelihood=Sheet1!$C$6:$G$6C1:C5
Cells with Data Validation
CellAllowCriteria
K1List=Locations
K2List=Risk_Category
K3List=High_Level_Risk_Categories
D][/XD][XD][/XD][/XR][/RANGE]
 
Upvote 0
Try it like
Excel Formula: 
=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2))*(IF($K$3="",1,isnumber(search($K$3,Risk_Table[Risk Category])))),""))
 
Upvote 0
Fluff said:
Try it like
Excel Formula: 
=TEXTJOIN(", ",,FILTER(Risk_Table[Index '#],(Risk_Table[Likelihood]=C$6)*(Risk_Table[Cost Impact]=$B1)*(IF($K$1="",1,Risk_Table[Location]=$K$1))*(IF($K$2="",1,Risk_Table[Risk Category]=$K$2))*(IF($K$3="",1,isnumber(search($K$3,Risk_Table[Risk Category])))),""))
Click to expand...

Interesting, I wish I could understand how that works :ROFLMAO:
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
601
pankajgrover
P
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
122
GMC The Macro Man
GMC The Macro Man
T
  • Solved
SUMPRODUCT formula not working
Replies
5
Views
246
thewiseguy
T
E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
277
Fluff
Fluff
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A

Forum statistics

Threads
1,223,885
Messages
6,175,186
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top