Conditional Coloring based on relative cell value

Question

I know how to do Conditional Coloring.

I also know how to use the LookUp() command.

What I would like to know is how to color a cell when two lookup calls generate different results (see example below). As a side note, I find it absolutely astounding how if you google conditional coloring based on another cell, it foolishly assumes that no one would want to compare one cell to its neighbour, but instead uses a fixed cell.

Worksheet 1: Proposed IP Schema

MAC Address       IP Address
AA:AA:AA:AA:AA:AA 192.168.1.1
BB:BB:BB:BB:BB:BB 192.168.1.2

Worksheet 2: Data from router/switch

MAC Address       IP Address
AA:AA:AA:AA:AA:AA 192.168.1.1
BB:BB:BB:BB:BB:BB 192.168.1.66

Worksheet 3: Master Document

MAC Address       IP Address
AA:AA:AA:AA:AA:AA 192.168.1.1
BB:BB:BB:BB:BB:BB There is an IP address mismatch

I know how to cheat and color the cell red by issuing a unique string (in this case There is an IP address mismatch) then conditionally color that cell red when it encounters the string There is an IP address mismatch, but is there a cleaner way to do this. I would like to keep the proposed IP address, but color the background red to indicate that there is an error.

Answer 1

Try this:

• get proposed address in master sheet second column
• use this in conditional formatting criteria: =NOT(B2=VLOOKUP(A2,actual!A:B,2,FALSE))