conditional formatting to keep track of difference in two data sets

Question

I am looking for a way to keep track of the differences in two similar data sets, using conditional formatting.

Example sheet

on the local sheet I would like to see the cells that have different values highlighted, on the global sheet I would like to see just the missing "unique IDs" highlighted.

Since the order of the lists can vary, I am at a loss as how to find cell differences, based on the differences in a row, with the same unique ID, so order wouldn't matter.

Any help or nod in the right direction would be much appreciated.

Answer 1

try:

=NOT(REGEXMATCH(A2, TEXTJOIN("|", 1, INDIRECT("local!A2:A"))))*(A2<>"")

---

and:

=INDEX(NOT(REGEXMATCH($A2&$B2&$C2, TEXTJOIN("|", 1, INDIRECT("global!A2:A")&INDIRECT("global!B2:B")&INDIRECT("global!C2:C")))))*(A2<>"")