Forum Discussion
Solved
Check if a cell contains a word in listA, if not, then check if it contains a word in listB etc..
Hi there! little background: I have two sheets: "Sheet1" and "Keywords", the "Keywords" sheet has two keywords columns ( H & G ) and a category column ( D ), and "Sheet1" has a description colum...
=TOROW(IFERROR(FILTER(Keywords!$D$213:$D$300,
DROP(N(
REDUCE("",SEQUENCE(88),
LAMBDA(u,v,VSTACK(u,IF(C2="Incident",
AND(
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*")),
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"))))))))
,1)),
"not incident"))
This should work if i correctly understand what you want to do. The formula is in cell H2 in the attached sample file and filled down. If there are two or more matches for the search criteria in columns E and G of Sheet1 the formula spills the results to the right.
=TOROW(IFERROR(FILTER(Keywords!$D$213:$D$300,
DROP(N(
REDUCE("",SEQUENCE(88),
LAMBDA(u,v,VSTACK(u,IF(C2="Incident",
AND(
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$G$213:$G$300,v),,"،") & "*")),
OR(COUNTIF(E2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(INDEX(Keywords!$H$213:$H$300,v),, "،") & "*"))))))))
,1)),
"not incident"))
This should work if i correctly understand what you want to do. The formula is in cell H2 in the attached sample file and filled down. If there are two or more matches for the search criteria in columns E and G of Sheet1 the formula spills the results to the right.
Bravo excel master! this is exactly what I wanted and more.
Thank you so much, I really appreciate it!!
Thank you so much, I really appreciate it!!