If two cells match but two others do not

[mikec82]

I've got two sheets that I'm trying to compare against each other, to help locate discrepancies between the two. They both have the same columns, although they might be in different column orders. So Sheet1 might look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Grade[/TD]
[TD]Birthday[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Michael Smith[/TD]
[TD]8[/TD]
[TD]05-01-01[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]234567[/TD]
[TD]Jennifer Smith[/TD]
[TD]9[/TD]
[TD]01-01-00[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

And Sheet2 might be:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Birthday[/TD]
[TD]Grade[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]Jennifer Smith[/TD]
[TD]234567[/TD]
[TD]12-12-12[/TD]
[TD]9[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Michael Smith[/TD]
[TD]123456[/TD]
[TD]05-01-00[/TD]
[TD]8[/TD]
[TD]M[/TD]
[/TR]
</tbody>[/TABLE]

I'd like to set up a conditional formatting that would highlight the discrepancies, like the example Sheet2!C2 above.
For that example, the formula would need to find the matching ID # in Sheet1, and format it if the Birthdays didn't match (Sheet2!C2 and Sheet1!D3 in this case). Does that make sense? I'm not sure if I would use an Index function, or maybe IsNumber?

 

[oldbrewer]

[TABLE="width: 889"]
<colgroup><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]col B[/TD]
[TD]col C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Grade[/TD]
[TD]Birthday[/TD]
[TD]Gender[/TD]
[TD]row 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]Michael Smith[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]05/01/2001[/TD]
[TD]M[/TD]
[TD]row 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234567[/TD]
[TD]Jennifer Smith[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]01/01/2000[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]fred[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]03/04/2005[/TD]
[TD]M[/TD]
[TD]row 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Birthday[/TD]
[TD]Grade[/TD]
[TD]Gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jennifer Smith[/TD]
[TD="align: right"]234567[/TD]
[TD="align: right"]12/12/2012[/TD]
[TD="align: right"]9[/TD]
[TD]F[/TD]
[TD]row 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael Smith[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]05/01/2000[/TD]
[TD="align: right"]8[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]03/04/2005[/TD]
[TD="align: right"]7[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]jennifer smith birthday (in C10) has turned red because of this conditional format formula in C10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=OFFSET($B$3,MATCH(A10,$B$4:$B$6,0),2)<>C10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Select Sheet2!C2.

Then MATCH(Sheet1!$1:$1, C$2, 0 ) is the column on sheet1 for Birthday
and MATCH(Sheet1!$A:A, $B2, 0) is the row number for ID 234567.

So if you set a CF formula of =(C2<>INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$2, 0))) on sheet2 C2 and copy that to the rest of sheet 2, it should do what you want.

 

[mikec82]

Thanks to both for the response. I'm working first on oldbrewer's. I wondered if you could explain what the $B$3 refers to? Does that just mean that it is offsetting 2 from that location? I'm not sure what it would translate to in my original example, in other words. The other part I think I have converted correctly:

=OFFSET(BB!$D$2 (?),MATCH($P$2,BB!$A:$A,0),2)<>C2

How I believe it to translate (but am obviously wrong): Find P2's match in BB!A:A, and highlight C2 if does not equal whatever is in BB!D:D.

 

[mikec82]

Mike - I set up a workbook exactly like the example I used. When I add the below formula to Sheet2!C2, it doesn't highlight, even the two birthdays do not match. Am I doing something wrong?

=(C2<>INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$2, 0)))

I will point out that Birthday appears in Column D in Sheet 1 and Column C in Sheet 2, which may be what's throwing it off?

 

[oldbrewer]

B3 is starting point for the offset

 

[mikerickson]

What happens when you put the formula =INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$2, 0)) in cell Sheet2!C2?
(I understand that that cell is intended for something else, but this is just for testing)

It should return the Birthday of that person from sheet 1.

Are the headers on both sheets exactly the same, including any trailing blanks?

 

[mikec82]

It returns a zero.

Edit: I transferred the test into a Google Sheet (below). I know Google Sheets work differently, but for whatever reason, C2 returns an error there.

https://docs.google.com/spreadsheets/d/10EN_lmjopEOU2T1fH97Omgh6vNkOkCWF95HNUiSGQJE/edit?usp=sharing

 

[mikerickson]

That pointed out my error

=INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$1, 0))

 

[mikec82]

Thanks for sticking with me on this one Mike!

I must be going crazy, because it is still not formatting the cell.

To confirm, here's Sheet1:

[TABLE="width: 321"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Grade[/TD]
[TD]Birthday[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Michael Smith[/TD]
[TD]8[/TD]
[TD]5/1/2001[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]234567[/TD]
[TD]Jennifer Smith[/TD]
[TD]9[/TD]
[TD]1/1/2000[/TD]
[TD]F
[/TD]
[/TR]
</tbody>[/TABLE]

And Sheet2:

[TABLE="width: 337"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Birthday[/TD]
[TD]Grade[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]Jennifer Smith[/TD]
[TD]234567[/TD]
[TD]12/12/2012[/TD]
[TD]9[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Michael Smith[/TD]
[TD]123456[/TD]
[TD]5/1/2000[/TD]
[TD]8[/TD]
[TD]M[/TD]
[/TR]
</tbody>[/TABLE]

If I put either formula below into Conditional Formatting for Sheet2!C2 (12/12/2012), it does not format the cell, even though Jennifer Smith's birthdays do not match. I know it's got to be something I'm not doing from my end - I wonder if it is related to array formulas? Not sure that cntl+shift+enter would work for CF. Sorry for the trouble.

=(C2<>INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$1, 0)))

=INDEX(Sheet1!$1:$5000, MATCH(Sheet1!$A:A, $B2, 0), MATCH(Sheet1!$1:$1, C$1, 0))