Google-sheets – Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet

conditional formattinggoogle sheets

I have two sheets:

The first is the Master Sheet of responses to a survey.

The second will be an Update Sheet auto-generated from the new responses.
New responses can be either entirely new rows or edits/updates to previous entries.

I am trying to create a Conditional Formatting rule to auto-highlight any rows in the Update Sheet which do not match the corresponding rows in the Master sheet.
(A row "does not match" when it shares the same Respondent ID number as the Master sheet row but contains different information in the following cells.)
I'm also trying to make it ignore any empty cells.

I created named ranges: "Master" and "Update" on each sheet respectively.

Here's an image of what I'm trying to accomplish:
Highlight Demo

I'd like to accomplish this without scripts or using a helper column if possible.

Any help from you genuine experts out there would be greatly appreciated!

And here's the Demo Sheet for reference.

So far I have tried to use INDIRECT to accomplish the cross-sheet highlighting (below). However, that didn't work so I'm obviously doing something wrong. 

CONDITIONAL FORMATTING:
- Apply to Range: A3:A200,F5:F200
- Custom Formula: =ISNA(match(A3,INDIRECT("Master!F5:AS"),0))

Update #2:

With help from @I'-'I I've been able to get closer with: 

CONDITIONAL FORMATTING: 
Apply to range: A3:F200
Custom Formula: =and(isna(match($A3,INDIRECT("Master!F5:F"),0)),not(isblank($a3)))

Highlight Demo v3

However, while it now highlights the "New Data" rows, it ignores the "Updated Data" row which has the same Respondent ID but different data in the following cells.

Any suggestions on how to rectify this would be appreciated!

Best Answer

=AND(NOT(ISBLANK($A3)),OR(IFERROR(ARRAYFORMULA(VLOOKUP($A3,INDIRECT("MASTER!F5:J"),COLUMN($A$1:$E$1),0)<>$A3:$E3),1)))
  • VLOOKUP to lookup ID(A3) in the master sheet and return the corresponding 5 columns(5 cells).
  • VLOOKUP results are compared against current row's 5 columns(<>A3:E3).
  • IFERROR to return TRUE if lookup fails(suggesting NewData).
  • ISBLANK to ignore blank rows.
  • AND/OR/NOT Self explanatory. Used to combine TRUE/FALSE in a specific way to achieve intended result.
Related Topic