# Excel: Conditional Format Rows in One List that are in Another List by David Hager

Here is the scenario. You have a long list with multiple columns and a second list containing records to look up in the first list. All of the records in each row of the first list must be compared with all of the records in the second list.

Here is the conditional formatting formula that will highlight the desired rows. Although it appears surprisingly simple, it took me quite a while to come up with this.

=FIND(CONCAT(\$A2:\$E2),CONCAT(\$I\$2:\$M\$4))

This formula looks for the concatenated row string in the long string from list 2 and if the FIND function finds the string, a value corresponding to the position of the string is returned. Any number of 1 and above is interpreted in CF as TRUE. Otherwise, the formula produces an error, which is interpeted as FALSE.

This CF formula was applied to list 1 starting at A2.

As you can see from the following figure, the expected rows in list 1 are highlighted.

Now, if your data happened to be all numbers in each column, a row could be hightlighted by coincidence due to a match in the large concat string. So, don’t use this technique with lists of that characteristic.

I am sure that you will find this technique useful.

TableCompareCF

## 3 thoughts on “Excel: Conditional Format Rows in One List that are in Another List by David Hager”

1. Peter Bartholomew

David
Have you any idea why conditional formatting should not accept a Boolean array? One is reduced to ‘tacky’ relative referencing using the conditional formatting calculation engine that will not even evaluate range intersections!
The formula I would wish to use is
=COUNTIFS(category1, category2, type1, type2)
but, instead, I have to mess around with
=COUNTIFS(category1, category2.item, type1, type2.item)
where ‘category2.item’ refers to the intersection
= category2 row
and ‘row’ refers to “=7:7” or “=8:8” depending on where the active cell is (or “=R” using a more rational notation).