Check if a cell contains a word in listA, if not, then check if it contains a word in listB etc..

[mrbatata]

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!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Check if a cell contains a word in listA, if not, then check if it contains a word in list
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[mrbatata]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thank you!
Sorry I didn't think of that earlier. However, I already got the answer in techcommunity forum , so thank you all

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Check if a cell contains a word in listA, if not, then check if it contains a word in list
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry I didn't know this.. my bad