Find specific term in a text string and return that term in the previous column

Question

The initial return is functioning for some reason that I cannot fathom, but when I pull down, the other results are returned as #VALUE!.

=IF(FIND("Drummondville",B3)>0,"Drummondville",
IF(FIND("Saint-Germain-de-grantham",B3)>0,"Saint-Germain-de-grantham",
IF(FIND("Saint-cyrille-de-wendover",B3)>0,"Saint-cyrille-de-wendover","")))

Answer 1

Try: You can either hard code the search words or create a reference range and name it as Search_Word and use with in a LOOKUP() Function

---

• Formula used in cell A2 --> Search Words hard-coded.

=LOOKUP(9^9,
SEARCH({"Drummondville","Saint-cyrille-de-wendover","Saint-Germain-de-grantham"},B2),
{"Drummondville","Saint-cyrille-de-wendover","Saint-Germain-de-grantham"})

---

• Formula used in cell A11 --> When Search Key Words use from a define range

=LOOKUP(9^9,SEARCH(Search_Word,B11),Search_Word)

---

With MS365 you could try using FILTER() XMATCH() & TEXTSPLIT()

---

• Formula used in cell A11

=LET(x,TEXTSPLIT(B11," "),
FILTER(x,ISNUMBER(XMATCH(x,Search_Word))))

---

With One Single Array Formula:

---

• Formula used in cell A11

=MAP(B11:B15,LAMBDA(m,LET(x,TEXTSPLIT(m," "),
FILTER(x,ISNUMBER(XMATCH(x,Search_Word))))))