Formula that finds the first and nearest to the beginning instance of a word

[daatr]

Hello,

I need a formula that looks for an exact word in a column whose individual cells might contain one or multiple words, and returns the first cell that contains that word closest to the beginning of the cell.

So for ex., if the word i'm looking for is dog, and

column a is:
House dog
Dog tree

-> then the formula would return dog tree, which is the first cell that contains the value dog in the most left positioning (the first word of the cell).

Please see attached excel file for more info. Thanks in advance.

beginning_Word_Example.xlsx

[John Vergara]

Hi daatr!

One option could be:

=INDEX(A$2:A$6,MATCH(AGGREGATE(15,6,SEARCH(" "&C2&" "," "&A$2:A$6&" "),1),SEARCH(" "&C2&" "," "&A$2:A$6&" "),)) 

Blessings!

[daatr]

Hi daatr!

One option could be:

=INDEX(A$2:A$6,MATCH(AGGREGATE(15,6,SEARCH(" "&C2&" "," "&A$2:A$6&" "),1),SEARCH(" "&C2&" "," "&A$2:A$6&" "),)) 

Blessings!

Perfect, that did it. Thank you very much.