How to match 3 columns in 2 different sheets

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
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

Code:
=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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=IF(COUNTIFS('Other sheet'!A:A,A2,'Other sheet'!B:B,B2,'Other sheet'!C:C,C2),"Yes","No")
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Hi Honkin,

You could do it with MATCH

Book1
ABC
1LeagueDate TimeMatch
2A1-Jan-21 14:00Dog-Cat
3A1-Jan-21 14:00Dog-Cat
4A1-Jan-21 14:00Dog-Cat
5B1-Jan-21 14:00Dog-Cat
6B1-Feb-21 15:00Dog-Cat
7B1-Feb-21 15:00Dog-Cat
8B1-Feb-21 15:00Dog-Cat
9B1-Feb-21 15:00Cat-Mouse
10B1-Jan-21 14:00Dog-Cat
11B1-Jan-21 14:00Dog-Cat
Lay Over 2.5


Book1
ABCD
1LeagueDate TimeMatchMatch?
2A1-Jan-21 14:00Dog-CatYes
3A1-Jan-21 14:00Dog-CatYes
4A1-Jan-21 14:00Cat-MouseNo
5B1-Jan-21 14:00Cat-MouseNo
6B1-Feb-21 15:00Dog-CatYes
7B1-Feb-21 18:00Dog-CatNo
8B1-Feb-21 15:00Cat-MouseYes
9B1-Feb-21 18:00Cat-MouseNo
10B1-Jan-21 14:00Dog-CatYes
11B1-Jan-21 14:00Cat-MouseNo
12B1-Jan-21 14:00Cat-MouseNo
13B1-Jan-21 14:00Cat-MouseNo
14B1-Jan-21 14:00Cat-MouseNo
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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 :oops:

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Limited 9999 range using COUNTIF gave 12 seconds.
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
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.
Excel 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

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
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.
Excel 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

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
Hi Honkin,

You could do it with MATCH

Book1
ABC
1LeagueDate TimeMatch
2A1-Jan-21 14:00Dog-Cat
3A1-Jan-21 14:00Dog-Cat
4A1-Jan-21 14:00Dog-Cat
5B1-Jan-21 14:00Dog-Cat
6B1-Feb-21 15:00Dog-Cat
7B1-Feb-21 15:00Dog-Cat
8B1-Feb-21 15:00Dog-Cat
9B1-Feb-21 15:00Cat-Mouse
10B1-Jan-21 14:00Dog-Cat
11B1-Jan-21 14:00Dog-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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top