Matching Between Two Columns on Two Different Sheets

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
TechnologyManguAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
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
SimonCommented:
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
TechnologyManguAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

TechnologyManguAuthor Commented:
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
SimonCommented:
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
TechnologyManguAuthor Commented:
If they matched row for row then this would be easy :)
0
TechnologyManguAuthor Commented:
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
SimonCommented:
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
TechnologyManguAuthor Commented:
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
SimonCommented:
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
TechnologyManguAuthor Commented:
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
SimonCommented:
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
SimonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TechnologyManguAuthor Commented:
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
byundtCommented:
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
SimonCommented:
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
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.