I have no clue

[downwithjunk]

Any help would be greatly appreciated.

I have a list of inventory (col A:C) with 3 criteria. I'm trying to figure out how to add conditional formatting to the table on the right (col E:I) that highlights the cells (g2:i10) if they are present in the list on the left. I've marked the cells with an "x" if they should be highlighted...for illustrative purposes.

	Size	Type				1	2	3
Apples	S	2		Apples	S		x
Oranges	S	3		Apples	M	x
Pears	M	1		Apples	L
Oranges	L	2		Oranges	S			x
Apples	M	1		Oranges	M		x
Apples	M	1		Oranges	L		x
Oranges	M	2		Pears	S		x
Pears	S	2		Pears	M	x
				Pears	L

<tbody>
</tbody>

 

[Max1616]

Hello, let's assume your first table lives in A:C and the second live in E:I. Follow these steps:

1. Select cells G2
2. Click Conditional Formatting -> New Rule -> Use Formula to determine which cells to format
3. In the formula box, use this formula: =SUMIFS($C:$C,$A:$A,$E2,$B:$B,$F2)=G$1
4. Choose your desired format and click OK
5. Click Conditional Formatting -> Manage Rules and change the Applies to box from =$G$2 to =$G$2:$I$10
6. Click ok and check to make sure it is working correctly.

Let me know how this does!

 

[Max1616]

Just realized your list contains duplicates (for example Apples M Type 1). Use this formula in step 3 instead of the one I gave you:
=IFERROR(SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9=$G2)*(($C$2:$C$9)/COUNTIFS($A$2:$A$9,$F2,$B$2:$B$9,$G2))),0)=H$1

 

[downwithjunk]

Just realized your list contains duplicates (for example Apples M Type 1). Use this formula in step 3 instead of the one I gave you:
=IFERROR(SUMPRODUCT(($A$2:$A$9=$F2)*($B$2:$B$9=$G2)*(($C$2:$C$9)/COUNTIFS($A$2:$A$9,$F2,$B$2:$B$9,$G2))),0)=H$1

This resulted in i2:i10 being highlighted, instead of the various cells.

but then i realized the columns were shifted, so i tweaked the formula to the following, and it worked. THANKS!!!!!!

=IFERROR(SUMPRODUCT(($A$2:$A$9=$E2)*($B$2:$B$9=$F2)*(($C$2:$C$9)/COUNTIFS($A$2:$A$9,$E2,$B$2:$B$9,$F2))),0)=G$1