Thanks for your kind explanation, please check whether the follow formual is helpful to you. Please note you need to press Ctrl+Shift+Enter to convert it to be an array formula.
=IFNA(INDEX($C$2:$C$9,MATCH(IFERROR(LEFT(E2,FIND(" ",E2)-1),E2),IFERROR(LEFT($B$2:$B$9,FIND(" ",$B$2:$B$9)-1),$B$2:$B$9),0)),"")
- The data of column E is picked from column A.
- I use the formula
IFERROR(LEFT(E2,FIND(" ",E2)-1),E2)to get the first word, such as the 'SHELL" for "SHELL SALE AT STORE #1234", the "7-ELEVEN for "7-ELEVEN".
And the formula
IFERROR(LEFT($B$2:$B$9,FIND(" ",$B$2:$B$9)-1)is to get the first word for arry B2:B9.
- Then I use Macth function to find the row number of of data in column B that with the same first word as column E.
- If there is no same word, the search result is empty.
Any misunderstandings, you can feel free to let me know.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.