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

user_5002322 0
Viewed 45

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.

enter image description here

=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"))))))
excel-formula excel
1 Answers

With Office 365 give a try on-

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

enter image description here

user_55147470
Related