Excel Match Data Multiple Columns

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?


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.

Category: microsoft excel Time: 2016-07-29 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.117 (s). 12 q(s)