How to match 3 columns in 2 different sheets

[honkin]

How is it possible to compare cells in 3 columns of 2 different sheets? The rows will not necessarily match up, but the column data will, so if A, B & C match in any row in the 2 sheets, then it is a yes.

Let me explain.

Column A is the name of the league
Column B is the date and time of the match
Column C is the name of the match, like Barcelona - Real Madrid

So you can see that although A or C might be the same over a span of some years, matching the date as well will mean that it is a particular match

What I am trying to do is to find out if matches appear in both sheets. The result is to go in 1 of those sheets called Lay Under 15_25_35, but the other sheet, which is in the same workbook, is called Lay Over 2.5.

So what I am after is IF A in the destination sheet = A in 'Lay Over 2.5'! AND IF B in the destination sheet = B in 'Lay Over 2.5'! AND IF C in the destination sheet = C in 'Lay Over 2.5'! then put "YES" otherwise "NO"

I tried writing it, but as the rows will not be the same, was struggling to get it right.

I tried the following, but to no avail

=IF(AND($A$2:$A2='Lay Over 2.5'!A2,$B$2:$B2='Lay Over 2.5'!B2,,$C$2:$C2='Lay Over 2.5'!C2),"YES","NO")

This returned NO on all occasion, even though there were some matches which appeared in both

Any suggestions on how to improve this so it puts YES if A, B & C match in any row between the sheets?

cheers

 

[jasonb75]

Countifs would be the best way to do that, the references to 'Other sheet' should be changed to the name of the sheet that is not holding the formula.

=IF(COUNTIFS('Other sheet'!A:A,A2,'Other sheet'!B:B,B2,'Other sheet'!C:C,C2),"Yes","No")

 

[Toadstool]

Hi Honkin,

You could do it with MATCH

Book1
	A	B	C
1	League	Date Time	Match
2	A	1-Jan-21 14:00	Dog-Cat
3	A	1-Jan-21 14:00	Dog-Cat
4	A	1-Jan-21 14:00	Dog-Cat
5	B	1-Jan-21 14:00	Dog-Cat
6	B	1-Feb-21 15:00	Dog-Cat
7	B	1-Feb-21 15:00	Dog-Cat
8	B	1-Feb-21 15:00	Dog-Cat
9	B	1-Feb-21 15:00	Cat-Mouse
10	B	1-Jan-21 14:00	Dog-Cat
11	B	1-Jan-21 14:00	Dog-Cat
Lay Over 2.5

Book1
	A	B	C	D
1	League	Date Time	Match	Match?
2	A	1-Jan-21 14:00	Dog-Cat	Yes
3	A	1-Jan-21 14:00	Dog-Cat	Yes
4	A	1-Jan-21 14:00	Cat-Mouse	No
5	B	1-Jan-21 14:00	Cat-Mouse	No
6	B	1-Feb-21 15:00	Dog-Cat	Yes
7	B	1-Feb-21 18:00	Dog-Cat	No
8	B	1-Feb-21 15:00	Cat-Mouse	Yes
9	B	1-Feb-21 18:00	Cat-Mouse	No
10	B	1-Jan-21 14:00	Dog-Cat	Yes
11	B	1-Jan-21 14:00	Cat-Mouse	No
12	B	1-Jan-21 14:00	Cat-Mouse	No
13	B	1-Jan-21 14:00	Cat-Mouse	No
14	B	1-Jan-21 14:00	Cat-Mouse	No
Sheet2

Cell Formulas
Range	Formula
D2:D14	D2	=IF(ISNA(MATCH(1,INDEX((A2='Lay Over 2.5'!$A$2:$A$9999)*(B2='Lay Over 2.5'!$B$2:$B$9999)*(C2='Lay Over 2.5'!$C$2:$C$9999),0,1),0)),"No","Yes")

 

[jasonb75]

You could do it with MATCH

You could make the match formula more efficient by using division with approximate match in place of multiplication with exact match, although I think that it would still be slower to process than countifs with large arrays.

 

[Toadstool]

You could make the match formula more efficient by using division with approximate match in place of multiplication with exact match, although I think that it would still be slower to process than countifs with large arrays.

I thought I'd try and verify timing so I made a version with 9999 rows in each table and no formula, put the formulae in a notepad, closed the workbook, reopened then pasted the notepad formulae down 9999 rows in two cycles, one for each formula.. I watched my 4 threads % complete until it finished and:
COUNTIF: 2 minutes 10 seconds
MATCH: 35 seconds

Yes, I'd have thought COUNTIF would be quicker also.

 

[jasonb75]

Did you limit the countif ranges to 9999 rows so that it was directly comparable?

Using entire columns in countifs and only 4k rows in match, I would agree with your timings.
Using

=IF(COUNTIFS('Lay over 2.5'!A$2:A$9999,A2,'Lay over 2.5'!B$2:B$9999,B2,'Lay over 2.5'!C$2:C$9999,C2),"Yes","No")

I'm seeing 10k formulas calculate in around 2 seconds.

Using the match array with entire columns would probably take about an hour

While I was doing a couple of quick tests for myself, the one thing that did surprise me was how little difference there was between approx and exact match.
I also tried a couple of things with aggregate and sumproduct, those and match were all very close in terms of the calc times.

 

[Toadstool]

Limited 9999 range using COUNTIF gave 12 seconds.

 

[honkin]

Countifs would be the best way to do that, the references to 'Other sheet' should be changed to the name of the sheet that is not holding the formula.

=IF(COUNTIFS('Other sheet'!A:A,A2,'Other sheet'!B:B,B2,'Other sheet'!C:C,C2),"Yes","No")

cheers Jason. Thanks for getting back to me and apologies for the delay. It took some time to check between both sheets to make sure it was all correct. Looks to be working perfectly.

Thanks again

 

[honkin]

Countifs would be the best way to do that, the references to 'Other sheet' should be changed to the name of the sheet that is not holding the formula.

=IF(COUNTIFS('Other sheet'!A:A,A2,'Other sheet'!B:B,B2,'Other sheet'!C:C,C2),"Yes","No")

cheers Jason. Thanks for getting back to me and apologies for the delay. It took some time to check between both sheets to make sure it was all correct. Looks to be working perfectly.

 

[honkin]

Hi Honkin,

You could do it with MATCH

Book1
	A	B	C
1	League	Date Time	Match
2	A	1-Jan-21 14:00	Dog-Cat
3	A	1-Jan-21 14:00	Dog-Cat
4	A	1-Jan-21 14:00	Dog-Cat
5	B	1-Jan-21 14:00	Dog-Cat
6	B	1-Feb-21 15:00	Dog-Cat
7	B	1-Feb-21 15:00	Dog-Cat
8	B	1-Feb-21 15:00	Dog-Cat
9	B	1-Feb-21 15:00	Cat-Mouse
10	B	1-Jan-21 14:00	Dog-Cat
11	B	1-Jan-21 14:00	Dog-Cat
Lay Over 2.5

2.5'!$C$2:$C$9999),0,1),0)),"No","Yes")[/XD][/XR][/RANGE]

Cheers ToadStool. Thanks so much for your reply. So it seems 2 ways to skin this cat. I had already implemented Jasonb75's answer, but at least it helps me understand the limitations of that one, with the 9999 range limit.

Thanks again