So I have two sheets with same type of data but for different months from different systems. I want to see if both systems have same data for each ID and if for each SK ID the org ID and Entity ID matches.

First sheet has 50,000 columns and second one is 150,000.

Columns :- A :- SK ID B :-Org ID C :- Entity ID

So SK ID in Sheet 1 should match SK ID in Sheet 2

AND For each SK ID that matches, does the Org ID and Entity ID associated with it also matches for both sheets? If not, then what doesn't match? Do all three differ or just Org ID is different rest matches, etc. There are various duplicates for IDs so that I'll take care of next.

One sheet has less rows than other one, so I'll be using the sheet with less rows to do the matching.

I tried using index, match, lookups, if statements, nothing seems to be working for some reason.

If it's possible for the output to be "Match" or "No Match" or maybe something where I can have output from the other table and then I can put a if statement to see if A1 matches B1 to C1, etc.

Again, I want to see if A1,B1,and C1 would match with ANY cell from A1-A150,000, B1-B150000, and C1-C150000, in other sheet. And if all match then maybe say "Match" and if one or other doesn't match then list or tell what doesn't?

  • Interest in an access solution? select * from table1,table2 where table1.colA=table2.colA and (table1.colB=table2.colB) and table1.colC=table2.colC; You could chop off some of the and statements for loser matching. – cybernard Jul 29 '16 at 21:03
  • A simple solution would be to add a vlookup column for each of the three columns and then a fourth one which would check if the three vlookups matched or not and provide appropriate output. But it takes 4 new columns... – zagrimsan Aug 4 '16 at 12:27

You should be able to do this with a formula like this:

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$150000,0)+MATCH(B1,Sheet2!$B$1:$B$150000,0)+MATCH(C1,Sheet2!$C$1:$C$150000,0)),"No Match","Match")

If it can't find one of them you get "No Match". If it finds all three you get "Match".

Edit: If you're checking that the are all on the same row, then you would use something like this:

=IF(ISNA(MATCH(A1&B1&C1,Sheet2!$A$1:$A$150000&Sheet2!$B$1:$B$150000&Sheet2!$C$1:$C$150000,0)),"No Match","Match")

This is an array formula so it needs to be entered using Ctrl+Shift+Enter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.