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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Size[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]S[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]S[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]M[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]L[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]M[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Oranges[/TD]
[TD]L[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]M[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]S[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]M[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pears[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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