Search data in one column and find the corresponding value in another column

Question

Hello Everyone

I have a list of data in column A with Vendor name, I want to search that Vendor name in column B and when the vendor name is found, it will return the Vendor category in column C. For example, search A4 will find B8 since SHELL is the vendor and return C8 which is GAS. This is just a sample data but I have a whole lot more, below is the picture, any help will be appreciated.

Answer 1

Hi @DataNerd

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.

---