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

DataNerd 21 Reputation points

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.


Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
2,836 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,514 questions
{count} votes

Accepted answer
  1. Emily Hua-MSFT 27,171 Reputation points

    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.

0 additional answers

Sort by: Most helpful