Finding match of two sets of data

[Gman33]

Hello everyone,

I am having a problem and I'm a newbie with excel so looking for some help!

I need to compare two sets of data and find a match, for example...

I need to compare User 1 Row with each Row of Group B to see if User 1 picked User 6 and User 6 also picked User 1 for a positive match. This needs to be done for all Group A users to all Group B users. If a match for Pick1, then "match pick 1" would display "match w/ user#"

Group A	Pick1	Pick2	Match Pick 1	Match Pick 2
User:1	6	7
User:2	7	9
User:3	6	10
User:4	9	10
User:5	10	8
Group B
User:6	1	2
User:7	1	2
User:8	2	3
User:9	2	3
User:10	4	5

<tbody>
</tbody>

Thank you,
Gman

 

[DRSteele]

Welcome to the forum.

If I understood correctly, this formula will report the phrase "Match User # X" when the choice by a Group A User matches the Choice of the Group B User.

I altered the data to simplify user numbers. Copy D2 across and down as necessary.

	A	B	C	D	E
1	Group A User	Pick1	Pick2	Match Pick 1	Match Pick 2
2	1	6	7	Match User# 6
3	2	7	9
4	3	6	10
5	4	9	10
6	5	10	8
7	Group B User
8	6	1	2
9	7	1	2
10	8	2	3
11	9	2	3
12	10	4	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet36​

Worksheet Formulas Cell Formula D2 =IF(INDEX(B$8:B$12,MATCH(B2,$A$8:$A$12,0))=$A2,"Match User# "&B2,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Gman33]

Thank you, I will check it out when I get home!

 

[Gman33]

Welcome to the forum.

If I understood correctly, this formula will report the phrase "Match User # X" when the choice by a Group A User matches the Choice of the Group B User.

I altered the data to simplify user numbers. Copy D2 across and down as necessary.

	A	B	C	D	E
1	Group A User	Pick1	Pick2	Match Pick 1	Match Pick 2
2	1	6	7	Match User# 6
3	2	7	9
4	3	6	10
5	4	9	10
6	5	10	8
7	Group B User
8	6	1	2
9	7	1	2
10	8	2	3
11	9	2	3
12	10	4	5

<tbody>
</tbody>

Sheet36​

Worksheet Formulas Cell Formula D2 =IF(INDEX(B$8:B$12,MATCH(B2,$A$8:$A$12,0))=$A2,"Match User# "&B2,"") <tbody> </tbody>

<tbody>
</tbody>

This did not work. Also it is static and doesn't roll down the list to check for matches.

 

[DRSteele]

What?

There is only one match in your sample data. The other green cells display blank because there are no matches.

 

[Gman33]

Ok must be user error on my part then, thanks

 

[DRSteele]

Perhaps you could post some more sample data together with the expected results for each datum. We can dig into it until your objectives are met.