Google Sheets: Compare each cell of a column seperately and check another cell in the found row for conditional formatting

Question

Hello all Sheet users out there.

I have a sheet with a list of resources with their production and usage being calculated on the left side and the overall prod/use being monitored on the right side.

       A       B       C       D   |     E            F             G             H
1    Input     In   Output    Out  |  Resource       totIn        totOut      effective
2    Iron      20   FeIngot    30  |  Iron               30  =SUMIF(...)  =totIn-totOut
3    Copper    20   CuIngot    20  |  Copper             25  =SUMIF(...)  =totIn-totOut
4    Stone     10   Gravel     50  |  CuIngot   =SUMIF(...)  =SUMIF(...)  =totIn-totOut
5    FeIngot   10   FePlate     5  |  FeIngot   =SUMIF(...)  =SUMIF(...)  =totIn-totOut
6    CuIngot   25   Wire       75  |  Stone              45  =SUMIF(...)  =totIn-totOut
7    CuIngot   10   Cable      20  |  Gravel    =SUMIF(...)  =SUMIF(...)  =totIn-totOut

The actual sheet would look more like this:

       A       B       C       D   |     E            F             G             H
1    Input     In   Output    Out  |  Resource        totIn       totOut      effective
2    Iron      20   FeIngot    30  |  Iron               30           20             10
3    Copper    20   CuIngot    20  |  Copper             25           20              5
4    Stone     10   Gravel     50  |  CuIngot            20           35            -15
5    FeIngot   10   FePlate     5  |  FeIngot            30           10             20
6    CuIngot   25   Wire       75  |  Stone              45           10             35
7    CuIngot   10   Cable      20  |  Gravel             50            0             50

On the left side, I want to mark all cells in column "In" red that have a negative effective production calculated on the right side. I thought about using the conditional formatting, looping through every text cell in the "Resource" column to find the one that equals the "Input" of the same row the cell I want to check is in and then check if the "effective" value of the "Resource" I found is less than 0. The problem is that I don't know how to loop through the values and store the matching row to check if the H value is negative.

Example 1: B6 is checked. A6 needs to be compared to every cell in E2:E and when there is a match, in this case E4, check if H4 is negative. It is, so there is formatting applied. Example 2: B3 is checked. A3 needs to be compared to every cell in E2:E and when there is a match, in this case E3, check if H3 is negative. It is not, so there is no formatting applied.

Is there any way that I can apply this formatting in the conditional formatting tool?

Keep in mind that my sheet is much more complex than these examples and it has about 120 resources that can't all be moved in order with the left side because multiple rows can use the same resource as input or output.

Thank you in advance for every ounce of your help.

Answer 1

try this formula =VLOOKUP($A1,$E:$H,4,false)<0 in conditional formatting