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?

 

[mikerickson]

There aren't any array formulas in that formulation.
But, it would really help if I got the order of the arguments of Match correct.

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