## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# Matching Between Two Columns on Two Different Sheets

Posted on 2014-11-21
390 Views
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
Question by:TechnologyMangu

LVL 35

Expert Comment

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

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

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

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

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

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

Author Comment

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

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

Author Comment

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

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

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

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

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

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

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!

Example-Multiple-Match-Q28567413.xlsx
0

LVL 18

Expert Comment

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

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

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateâ€¦
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.