Solved

Matching Between Two Columns on Two Different Sheets

Posted on 2014-11-21
18
366 Views
Last Modified: 2014-12-25
How do I match 2 columns in two separate sheets and get results in the second sheet to show me if there is a match, example:

Sheet 1:
A1 has Value X
A2 has Value Y

Sheet 2:
A1 has Value X
A2 has Value Y

I want Sheet 2 B1 to show me if there is a match for the combination of Sheet 1 A1 and A2 with Sheet 2 A1 and A2.
Example-Multiple-Match.xlsx
0
Comment
Question by:TechnologyMangu
18 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40458566
On sheet 2 cell c2 fill in the formula
=IF(A2='Sheet 1'!A2,IF(B2='Sheet 1'!B2,"Match","No Match"),"No Match")

Then copy down.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40458571
In a column to the right (e.g.  C1)

=AND(A2='Sheet 1'!A2,B2='Sheet 1'!B2)

This will give results TRUE or FALSE

If you want to convert it to text values "MATCH" and "No Match"
=if(AND(A2='Sheet 1'!A2,B2='Sheet 1'!B2),"Match","No Match")
0
 

Author Comment

by:TechnologyMangu
ID: 40458573
The data is not the same across rows - it could be in some cases, but not in all. I tried a simply IF statement, but think this requires more of a compound IF with a match/index kind of deal? Does that make sense?
0
 

Author Comment

by:TechnologyMangu
ID: 40458581
There are 500 values in each sheet with data in two columns, please see my example file. The data in two columns on sheet 1 I'd like to match to sheet 2 IF there is a match between both sheets, if there isn't then no match.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40458582
Do you want to compare which server each user is on?

Have you tried a vlookup?

e.g.
 =IF(VLOOKUP(A2,'Sheet 1'!A:B,2,FALSE)='Sheet 1'!B2,"MATCH","NO MATCH")
0
 

Author Comment

by:TechnologyMangu
ID: 40458585
If they matched row for row then this would be easy :)
0
 

Author Comment

by:TechnologyMangu
ID: 40458588
I want to compare which user has access to which server from sheet 1 to sheet 2 - Vlookup doesn't work. I think I need to do an IS/MATCH/INDEX between the two sheets.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40458610
Example-Multiple-Match-Solution.xlsx

Sorry, my vlookup formula in previous post was incorrect.

Please try this in sheet 2, C2 and fill down:
=IFERROR(IF(VLOOKUP(A2,'Sheet 1'!A:B,2,FALSE)=B2,"MATCH","NO MATCH"),"NO MATCH")

You can do the reverse match from sheet 1 C2:
=IFERROR(IF(VLOOKUP(A2,'Sheet 2'!A:B,2,FALSE)=B2,"MATCH","NO MATCH"),"NO MATCH")

It results in NO MATCH where the server is different for the same user, or if the user isn't listed on the other sheet, and MATCH where the user/server combination is the same, even if on a different line.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:TechnologyMangu
ID: 40458679
This almost worked, but the issue is, and I wish I could post more of an example, that it seems to only be matching when it finds a match for the first value in the area you create.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40458695
Do you mean it only results in MATCH if the same user is on the same server on both sheets? Isn't that the desired result.

I have just tested by making the first value in column A different on each sheet, but the other matching user/server combinations still correctly show MATCH.

Please see this example worksheet. If it isn't doing what you want, please extend the range of examples a little and indicate which rows are not returning the right result.
Example-Multiple-Match-Solution2.xlsx
0
 

Author Comment

by:TechnologyMangu
ID: 40458749
Sheet 1:

USER      SERVER
User 101      Server 1
User 2      Server 1
User 3      Server 2
User 4      Server 3
User 5      Server 4
User 1      Server 1

Sheet 2:
USER      SERVER      Result      Note
User 55      Server 1      NO MATCH      
User 2      Server 1      MATCH      
User 3      Server 2      MATCH      
User 4      Server 6      NO MATCH      
User 5      Server 4      MATCH      
User 1      User 1      NO MATCH      This user not listed on sheet 1

I want to check that users in Sheet 2 exist on Sheet 1 with access to the server being the same, so if user 1 has access to server 1 on sheet 1, next to that combination of user 1 and server 1 on sheet 2 I want to show a match.

I modified your sheet and this is the result - notice user 1 doesn't match even though the user is matching.
Example-Multiple-Match-Solution2-TM.xlsx
0
 
LVL 18

Expert Comment

by:Simon
ID: 40458756
User 1 doesn't match because you have written "user 1" in the server column. If you change it to "Server 1" it will match.
0
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40458768
For this to work, the users in column A must be unique - i.e. only one entry for each user ID. If there are multiple entries per user then vlookup will not work because it will match the first matching occurence of the user, so if you have

Sheet1:
User 1 server1
User 1 server2

Sheet2:
User1 Server2

You will not get a match because vlookup will return the first match (server1).

In this scenario of possible multiple connections per user, I'd suggest creating a compound key =A1&B1 on each sheet and then doing a vlookup using that compound key to check for matching user-server combinations.
0
 

Author Comment

by:TechnologyMangu
ID: 40458785
Yes, there are duplicates in BOTH columns, there are some unique entries but not many.

Anyone else have ideas here? If you need a better example let me know - I continue to believe that simple lookups here will not work - this has to be done, if I Am right, via some sort of nested match/index formula.

Help!!! :)
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 40458855
VLOOKUP is not going to be able to handle the problem unless you concatenate the values (as suggested by SimonAdept) in column A and B in both the lookup column and the value being sought (i.e. =A2 & ":" & B2, resulting in something like User1:Server1). I like to put a delimiting character between the values being concatenated to avoid the case of characters at the end of A2 being found at the beginning of B2. That's why the colon is in there.

As an alternative, consider using COUNTIFS in an IF statement:
=IF(COUNTIFS('Sheet 1'!A:A,A2,'Sheet 1'!B:B,B2)>=1,"MATCH","NO MATCH")

Cheers!

Brad
Example-Multiple-Match-Q28567413.xlsx
0
 
LVL 18

Expert Comment

by:Simon
ID: 40462147
Hi TechnologyMangu, hopefully one of the last two suggestions in this thread will provide your solution, as they both cope with duplicates in one or both columns, but if not, please post an extended sample that demonstrates the full range of scenarios.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40517440
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now