Conditional Formatting based on a cell with data separated by commas

[Technovice]

Hello,
I need some assistance with conditional formatting whilst using the index and match function. I would like cells in table 2 (on the right) to change colour when the first and second columns align with the boxes marked with X in the table on the left. I have managed to do this for cells in columns H and I where there is a single word, but it won't work for cells where there are multiple entries separated by a comma.
As an example, the formula I have used to match in conditional formatting for entries in column H to Column J (orange color) is: =IF(INDEX($A$2:$F$7,MATCH($H2,$A$2:$A$7,0),2)="X","TRUE")
To match Column I to column J (blue color), the formula is:
=IF(INDEX($A$2:$F$7,MATCH($I2,$A$2:$A$7,0),2)="X","TRUE")
I have been unable to get the formula to recognize when a word from column A is is present but separated by a comma in column I (e.g. in I10)
Any assistance would be greatly appreciated.

 

[DanteAmor]

Hi and welcome to MrExcel!

The following formulas also consider the name in each column.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Fruit	Jack	Mel	Sam	Tom	Jake		Fruit 1	Fruit 2	Jack	Mel	Sam	Tom	Jake
2	Banana	X		X				Banana
3	Watermelon		X	X		X		Watermelon	Banana
4	Oranges	X						Apple
5	Mandarine		X		X			Banana
6	Fig				X			Fig	Banana
7	Pear					X		Fig	Banana
8								Apple
9								Banana
10								Mandarine	Oranges, Pear
11								Watermelon	Oranges
12								Fig	Banana
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:N12	Expression	=SUMPRODUCT(($A$2:$A$7=$H2)*($B$1:$F$1=J$1)*($B$2:$F$7="X"))	text	NO
J2:N12	Expression	=SUMPRODUCT((ISNUMBER(SEARCH(","&$A$2:$A$7&",",","&SUBSTITUTE($I2," ","")&",")))*($B$1:$F$1=J$1)*($B$2:$F$7="X"))	text	NO

 

[Technovice]

Thankyou so much, it seems to work when I copied it over, except for the Jack column remains empty? I have selected the correct cells to apply the formula to so I am not sure why?

 

[DanteAmor]

I share my file for you to review:

cf by commas

 

[Technovice]

Thankyou! It works!!