Highlight Excel Cells Using Conditonal Formatting Across Sheets

0
votes

I am using Excel 2010. Using CONDITIONAL FORMATTING I am trying to highlight cells based on the following criteria.

Sheet 1 ID (A2) must equal Sheet 2 ID (A2) (they must match)
and then if Sheet 2 Action equals "Insert", highlight the cell.

What I've tried
=AND(Sheet1!$A2=Sheet2!$A2,Sheet2!$B2="INSERT")

I would expect the cells with ID's 10, 4 and 2 to be highlighted in the linked image. enter image description here

Appreciate any help. Thanks.

1
excelexcel-2010conditional-formatting
That looks correct to me. What range did you apply it to? – jsheeran
Sorry I should have shown in the example data that the ID in sheet 2 is not in order i.e it could go 10, 5, 3, 1 etc. The range I am applying is =$A$2:$A$11 – PipeHelp
So you're checking for presence in both lists, rather than both being in the same position? Try =IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$11,2,FALSE)="Insert",FALSE) – jsheeran
Thanks for your help that seemed to do the trick. Much appreciated. – PipeHelp
No problem - I've posted it as an answer. – jsheeran

1 Answers

-1
votes

Your attempt compares each value in Sheet1 to the cell in the same position in Sheet2. To check for that value anywhere in your data range in Sheet2, and for the corresponding value in column B, use this instead:

=IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$11,2,FALSE)="Insert",FALSE)