Check to see if a cell contains text from a list then return the corresponding text

I have a column of car manufacturers BMW, Ford, Acura, Chevorlet etc. and I also have a list of auto dealers like Bob's Ford, BMW of Anytown, or Smith Chevorlet. I want to check if the auto dealers list contains the text from my list of car manufacturers and then return the value from the list in the adjacent cell.

So.....

A1 A2
Bob's Ford Formula to reference "Ford"

Any help is appreciated. Thanks.

Wow. I thought this was impossible in Excel, so I used a Perl script to do it. (Happy to share if interested, but if this works, no need.)

Originally Posted by jeff g

Wow. I thought this was impossible in Excel, so I used a Perl script to do it. (Happy to share if interested, but if this works, no need.)

Thanks for the welcome and the help. I think I was a little unclear as to what I needed. I've attached a sample spread sheet to show what I wanted the value to return. If there is a dealership named "Bob's Ford" and another one named "Ford of USA", I want the the adjacent cell to referene "Ford" from a list of car manufactuers.

Thanks and sorry I was a bit unclear.

car manufacturer look up.xls

Originally Posted by tigeravatar

crousseau989,

Attached is a modified version of your posted workbook. In cell B2 and copied down is this formula:

Please Login or Register  to view this content.

Awesome! Thanks for the help!

Thanks so much for these replies. The first one works perfectly for what I need. You have saved me a lot of time and frustration!!

Best
Rialtmann

great formula ! solved our problem as well, thank you

Hello Everyone

This formula is a great help to me also however I do have a question

I want the result to be able to show multiple terms from the search terms and index.

for example

Bob's BMW and Ford

Result would be

BMW,Ford

Rather than just Ford

Is this possible?

Any help would be greatly appreciated and beneficial

Thanks

Samuel