Search code examples
excelconditional-formatting

Compare cell data in Column B to all of Column A and turn green when there is a match

In excel, I have the following:

enter image description here

I am trying to find the quickest and most user-friendly way to compare columns 2 and 1.

If a name in column 2 (in the format shown below) matches ANY of the cells in Column 1, the cell in Column 1 goes GREEN, indicating that user is PRESENT in Column 2.

I have played around with conditional formatting but would appreciate any direct and efficient solutions.

I was trying to adapt a version of this, but it failed to work:

=SUMPRODUCT(--(MID($A4&" "&$A4,FIND(",",$A4)+2,FIND(",",$A4&" "&$A4,FIND(",",$A4)+2)-FIND(",",$A4)-2)=$B$4:$B$33))

where the above formula was added on highlighting the first column and then ensuring the A4 referred to the first item in Column 1 and the B was the first and last item in column 2. As mentioned, this didn't work.

Update

Tried this as well but didn't work.

enter image description here

Solution

  • This answer written assuming column1 = column A, and column2 = column B

    1. Apply a conditional format on column 1, set up with green background, with the formula =COUNTIF(B:B, A1)>0
    2. Apply another conditional format on Column 1, set up with red background, formula =COUNTIF(B:B, A1)=0

    Edit those formulas as necessary so that:

    • B:B is the address of column 2, the range you're looking in for each name
    • A1 is the address of the first (top-left) cell that the conditional format applies to.