Solved

Matching Between Two Columns on Two Different Sheets

Posted on 2014-11-21
18
357 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
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
If they matched row for row then this would be easy :)
0
 

Author Comment

by:TechnologyMangu
Comment Utility
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:SimonAdept
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:TechnologyMangu
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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:
SimonAdept earned 250 total points
Comment Utility
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
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 250 total points
Comment Utility
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:SimonAdept
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 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

17 Experts available now in Live!

Get 1:1 Help Now