Conditional formatting comparing tables for missing rows and/or changes

Question

I'm being dumb here. I don't know what I'm forgetting.

I have two tables on two sheets with similar structures. I would like to have the following conditional formatting rules: -

• By matching on the first three columns, highlight cells in those three columns in Sheet1.Table1 that do not exist in Sheet2.Table2.
• By matching on the first three columns, highlight cells in column four in Sheet1.Table1 that has a different value to that in Sheet2.Table2. The row must be in both tables.

The tables are sorted differently so I can't do a simple cell=cell comparison. I tried using the MATCH function, i.e. MATCH(blah)=0, but that didn't seem to do anything. I will eventually like to do this in VBA, but for now I just want to get the formulas correct and then I can write the code.

Basic example data below: -

• Following formatting rule 1 then the cells of Table1 in rows 3 & 4 within Column1:Column3 would be highlighted.
• Following formatting rule 2 then the cells of Table1 in rows 1 & 6 within Column4 would be highlighted.

Table1

Column1	Column2	Column3	Column4	Column5
asd	1qa	abc	Yes	blah
qwe	2ws	xyz	No	foo
zxc	3ed	def	Yes	blah
qwe	4rf	ghi	Yes	foo
asd	2ws	ghi	Yes	blah
rty	2ws	abc	No	foo

Table2

Column1	Column2	Column3	Column4
asd	1qa	abc	No
qwe	2ws	xyz	No
asd	2ws	ghi	Yes
rty	2ws	abc	Yes

Thanks in advance.

Answer 1

For the first part of the question, you can use Countifs (or Match if you prefer) but must use Indirect for each table reference:

=COUNTIFS(INDIRECT("Table2[Column1]"),INDIRECT("Table1[@Column1]"),INDIRECT("Table2[Column2]"),INDIRECT("Table1[@Column2]"),INDIRECT("Table2[Column3]"),INDIRECT("Table1[@Column3]"))=0

Similarly the second part formula would be

=COUNTIFS(INDIRECT("Table2[Column1]"),INDIRECT("Table1[@Column1]"),INDIRECT("Table2[Column2]"),INDIRECT("Table1[@Column2]"),INDIRECT("Table2[Column3]"),INDIRECT("Table1[@Column3]"),INDIRECT("Table2[Column4]"),"<>"&INDIRECT("Table1[@Column4]"))>0

EDIT

This is a bit tedious, but you can avoid the indirect by defining the structured references in the name manager (mentioned briefly here)

e.g.

T1C1R2 is defined as

Table1[@Column1]

and T2C1 is defined as

Table2[Column1]

so the first formula becomes

=COUNTIFS(T2C1,T1C1R2,T2C2,T1C2R2,T2C3,T1C3R2)=0