If cell contains a word from a specific array...

Hey guys,

I'm having trouble with my excel sheet... and I have been stuck here for several hours!

I have an excel sheet with in column A the names of major retailers + location. For example:

A
IKEA City A
IKEA City B
Target City A
Target City C
Walmart City D
....
....

This list continues for quite a bit. What I want to achieve is to get a list of names in column B of the retailers WITHOUT the city name. I only need to do this to a certain amount of retailers. My first thought was to use an array there I first write down the conditions and what results I want if the conditions are met. For example:

IKEA*	IKEA
Target*	Target
Walmart*	Walmart

This unfortunately did not end well

Vlookup does not work, it works the other way round (while the condition of IKEA*=IKEA City A holds, IKEA City A=IKEA* does not). This holds the same for the Match function.

Any clue how I can solve this?

Book1.xlsx

Sometimes I want more than the first word and instead want to change it to its abbreviation.

I added the spreadsheet to this post. You can find in column A the names of the retailers plus some extra words.

In column D you can find the words I want to find in column A and the result I want to get in column E.

For example, I want in C5:C11 "carrefour" and C23:C26 "dreamland" etc.