Match two columns over two sheets

[HangMan]

I have a situation where I need to find matches in two columns (combined) on two sheets, so for example, if the entries in A2 and B2 match the entries in A5 and B5 on the second sheet, this is a match. Both sheets must show an exact match, so if on sheet 1, A2 says '1 Capital Hill' and B2 says 'Cat' and then on sheet 2, A5 say's '1 Capital Hill' and B5 say's 'Dog', then this is NOT a match, both fields have to match.

I have an excellent macro, created by protonLeah which checks for matches between sinlge columns on two sheets which works really well, but I have no idea how this could be adapted to achieve the above?

This macro runs a comparison and put the cell reference for the match on the second sheet in a user specified column. I need the same to outcome, but based on checking two columns at once.

Is this possible?

I've attached a very simple basic sample to demonstratewhat I'm trying to achieve.

Please Login or Register  to view this content.

[stunn]

Based on your example sheet and your stated requirements, I can't see the need for VBA. Try this in H5:

Please Login or Register  to view this content.

and this in I5:

Please Login or Register  to view this content.

[HangMan]

Hi Stunn,

Okay, I tried that, adding the first formula to H5 and copying down, the second formula to I5 and copying down, but ended up with either a #NAME? or a "-". I'm assuming the "-"s are correct, as they show the 'Non Matches', but unsure why I'm getting the #Name? error and what I should be seeing instead?

[stunn]

For some odd reason, the forum text-editor has introduced spaces in the first formula! It should have been:

=IF(COUNTIFS($E$5:$E$17,$A5,$F$5:$F$17,$B5),"E"&MAX(INDEX(($A5&"|"&$B5=$E$5:$E$17&"|"&$F$5:$F$17)*ROW($E$5:$E$17),)),"-")

I can't see why, but spaces keep appearing in the formula. RO W should in fact be ROW

[Cutter]

Yes, the formula tags have a tendency to do that and it has been reported. Using code tags, instead, is an option.

Please Login or Register  to view this content.

[stunn]

Thanks Cutter, I'll keep that in mind for future reference.