Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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
0
TechnologyMangu
Asked:
TechnologyMangu
2 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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 LissRetired ProgrammerCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now