IF cell Contains "text" then lookup "Colour"

[vxs]

Hi all,
I need to format a column/set of cells where each cell has some text in it, and depending on a defined list elsewhere that text does a contains lookup to see whether to colour that cell in Green/Amber or Red. I was going to use the conditional formatting traffic lights but I dont know how to set this rule and do the lookup.

I could use the table below to find out or lookup what colour to use OR the first column with VW in it, in my spreadsheet already has the VW cell conditioned to the correct background colour so could just ask the cell to copy that formatting of cell contains the word VW?

Either-way I am not sure how to go about this, would preferably like to use the traffic light conditional formatting optional but don't know how to match these to the table below. How would I go about this?

Example

VW	Green
Fiat	Amber
BMW	Red
Land Rover	Amber
Kia	Green
Skoda	Amber
Honda	Red
Jeep	Red

<tbody>
</tbody>

Any help much appreciated.

 

[synergy16]

so you have a column of words and you want those words to be in cells that have a certain background color based on the example set above?

 

[Eric W]

I see a couple of options. The CF icons are nice, but they don't allow as flexible formulas as you can do when you design your own.

When you said that the column contained some text, I wasn't sure if it contained just the name of the car (Kia) or Kia was in a sentence. I designed a formula that handles both.

	G	H	I	J	K	L
1
2
3		List			Car	Color
4		My VW is pretty.			VW	Green
5		I own a Nissan.			Fiat	Amber
6		Land Rover			BMW	Red
7		Jeeps are tough.			Land Rover	Amber
8		Fiat			Kia	Green
9		Kia			Skoda	Amber
10					Honda	Red
11					Jeep	Red
12
13

<tbody>
</tbody>

Sheet8​

The lookup table I have in K3:L11, and my list is in column H. Select column H, click Conditional Formatting > New Rule > Use a formula > and enter:
=SUMPRODUCT(--ISNUMBER(SEARCH($K$4:$K$11,H1)),--($L$4:$L$11="Green"))*(H1<>"")

Then click on Format... and pick a green fill color.

Repeat the steps twice more, changing Green to Amber and Red, and you'll get the layout as shown above.


Second option is to put this formula in I4:
=MATCH(INDEX($L$4:$L$11,MIN(IF(ISNUMBER(SEARCH($K$4:$K$11,H4)),ROW($K$4:$K$11)-ROW($K$4)+1))),{"Red","Amber","Green"},0)
confirm it with Control+Shift+Enter, and drag down.

Then select column I and pick the traffic lights icon set. You'll get the traffic lights in the next column. (The screen shot doesn't show the icons.)
Row 5, which has no match, gets green because green is the first color in L4:L11.

	H	I	J	K	L
3	List			Car	Color
4	My VW is pretty.	3		VW	Green
5	I own a Nissan.	3		Fiat	Amber
6	Land Rover	2		BMW	Red
7	Jeeps are tough.	1		Land Rover	Amber
8	Fiat	2		Kia	Green
9	Kia	3		Skoda	Amber
10				Honda	Red
11				Jeep	Red
12

<tbody>
</tbody>

Sheet8​

Let me know if either works for you.