Forum Discussion
Excel function for searching for a word in a cell
Cell A1= FAD JAW ASH
Cell B1= BSA
Cell B2= BSD
Cell B3= JAW
i need a function that searches for the words B1, B2 and B3 in A1.
In this case JAW should be found in A1. If the function cant find any words in A1 it should say something like no words found
2 Replies
- If 2 or more words are found in A1, the formula of SergeiBaklan returns the first match while this formula returns the last match:
=IFNA(LOOKUP(2,1/(
COUNTIF(A1,”*”&B1:B3&”*”)),
B1:B3),”No words found”) In C1
it could be
=IFNA(INDEX($B$1:$B$3,MATCH(1,INDEX(--ISNUMBER(SEARCH($B$1:$B$3,$A$1)),0),0)),"nothing")
