How to check if an excel column text contain specific list of words withouot using IF statement?-CodePudding

I'm trying to check if X2 contains any of the text values given in AB2:AB7 and if matches then it returns the corresponding AC value.

I could use below nested IF formula, or would there be any other formula I can achive this easily. I do not want to use VBA loop. Only looking for an Excel formula.

=IF(ISNUMBER(SEARCH(AB2,X2)),AC2,IF(ISNUMBER(SEARCH(AB3,X2)),AC3,IF(ISNUMBER(SEARCH(AB4,X2)),AC4,IF(ISNUMBER(SEARCH(AB5,X2)),AC5,IF(ISNUMBER(SEARCH(AB6,X2)),AC6,IF(ISNUMBER(SEARCH(AB7,X2)),AC7,"Not Matching"))))))

CodePudding user response：

With Office 365 give a try on-

=@FILTER($F$2:$F$7,ISNUMBER(SEARCH($E$2:$E$7,A2)))