enter image description here

Hi im stuck more than 6 hours finding formula for this simple table, what the best formula for B2:B7 ? already use the filter-search, using vlookup also not work :

=filter(E:E,SEARCH(E2,A:A))
=VLOOKUP(A2;$D$2:$E$7;2;FALSE)

Please use the data on column D to show on column B

CodePudding user response：

Try

=index(RegexExtract(A2:A7,".*\s(.*)"))

Or this

=index(RegexExtract(A2:A7,"(?i)\b"&join("\b|\b",D2:D4)&"\b"))

The first formula extracts the last word, the second one extracts the first word in A2:A7 that matches any of the words in D2:D4.