Check if a cell contains a word in listA, if not, then check if it contains a word in list

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 column (Column E).

Requirement:
The description cell should contain keywords from both keyword columns ( at least one from H and one from G), if it does, then the formula should return the category ( D ), if it doesn't however, then it should check it against the next row in the keywords column and so on.

What I have done:

=IF(
C2="Incident",
IFS(
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$213,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$213,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$213,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$213,, "،") & "*"))),
Keywords!$D$213,
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$214,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$214,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$214,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$214,, "،") & "*"))),
Keywords!$D$214,
.....
.....
.....
AND(OR(COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$G$300,,"،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$G$300,,"،") & "*")), OR(
COUNTIF(E2, "*" & TEXTSPLIT(Keywords!$H$300,, "،") & "*"),COUNTIF(G2, "*" & TEXTSPLIT(Keywords!$H$300,, "،") & "*"))),
Keywords!$D$300
),
"not incident"
)

The problem with this code:
this code works perfectly, but the issue is that excel limits me to ~8000 characters, so I can't go through the whole keywords columns.


Is there more efficient way of doing this? a simple FOR loop would suffice but excel doesn't support them afaik.

Your help is much appreciated!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. I shall do it for you this time as you are new here: https://www.mrexcel.com/board/thread...b-etc.1254812/)

Cross posted question... same question appears in the MrExcel forum.

Please use the Report Post button. Thanks.

If it wasn't Mr Excel, then you MUST post the link - add ONE space to it so that you can post it.

Then mark the thread as solved. And read our rules, please.