Forum Discussion
Check if a cell contains a word in listA, if not, then check if it contains a word in listB etc..
=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.
I'm guessing you'd want to pull multiple categories where applicable. Also, I've included a 'delim' parameter so you can add or remove those as needed.
Try this:
=LET(
delim, {" ", ","},
GetCategory, LAMBDA(string,
LET(
words, TEXTSPLIT(string, delim),
c, COUNTA(words),
check_g, N(words = Key_1),
check_h, N(words = Key_2),
m, SEQUENCE(c, , 1, 0),
vector, MMULT(check_g, m) + MMULT(check_h, m),
categories, FILTER(Category, vector = 2, "Not Found"),
TEXTJOIN(", ", , categories)
)
),
BYROW(Description, GetCategory)
)
This also does what I want, I wish I could mark both answers as best answers.
Thank you so much, God bless you 🙌