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
 

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")
If I am not stretching the friendship, may I possibly ask a followup question on this please, Jason?

I just realised the potential of this, as I also need to bring a load of other data in from the same sheet. I assume that instead of having it return Yes or No, it could be used to bring in the value in a cell instead.

So based on your original code, I tried the following to bring in the corresponding data in from column R, but the formula just sits there in the cell and performs nothing

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

As you can see, I simply changed "Yes" to "'Other sheet'!R:R,R2" If I remove the "", it says too many arguments

Any thoughts on just that last section relating to column R

cheers mate
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows
The countifs method can only be used to see if a comparable entry exists or not, it can not tell you where it was found in order to return a corresponding text result.
To do that would need a different method, one like
Excel Formula:
=IFERROR(INDEX('Other Sheet'!$R:$R,AGGREGATE(15,6,ROW('Other sheet'!$A$2:$A$9999)/('Other sheet'!$A$2:$A$9999=A2)/('Other sheet'!$B$2:$B$9999=B2),1),"")
Which will pull the value from column R corresponding to the first row that matches the criteria in columns A and B.
Changing the 1 at the end to a 2 will pull the second match and so on.

Note that only the first part (index range) refers to a full column. Using entire columns for the other parts will result in very slow calculation times.
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
The countifs method can only be used to see if a comparable entry exists or not, it can not tell you where it was found in order to return a corresponding text result.
To do that would need a different method, one like
Excel Formula:
=IFERROR(INDEX('Other Sheet'!$R:$R,AGGREGATE(15,6,ROW('Other sheet'!$A$2:$A$9999)/('Other sheet'!$A$2:$A$9999=A2)/('Other sheet'!$B$2:$B$9999=B2),1),"")
Which will pull the value from column R corresponding to the first row that matches the criteria in columns A and B.
Changing the 1 at the end to a 2 will pull the second match and so on.

Note that only the first part (index range) refers to a full column. Using entire columns for the other parts will result in very slow calculation times.
Thanks so much for getting back to me Jason. Understood re countifs' limitations. Another one for me to tuck into my memory.

Can iferror have more than just the 2 criteria, as this still needs to use columns A, B & C for confirmation the matches are the same

Incidentally, this appears to missing some closing parenthesis; 6 opening and only 5 closing

cheers
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
Just quick follow up Jason. In the end, I had a rethink and really only need to reference 2 columns, as I guess if, as an example, Barcelona - Real Madrid is on a certain date, then the league is going to be a given, so column A is not vital. So I took your code and changed A & B to B & C. It still says it is missing a parenthesis, so I tried it in a number of locations, but the result comes back blank most of the time, even though the price of 1.41 is alongside the first match in the list. I have tried the closing parenthesis in all positions, but the only one which actually gives ANY result at all is this.

Code:
=IFERROR(INDEX('Lay Over 2.5'!!$R2:$R9900,AGGREGATE(15,6,ROW('Lay Over 2.5'!$B$2:$B$9900)/('Lay Over 2.5'!$B$2:$B$9900=B2)/('Lay Over 2.5'!$C$2:$C$9900=C2),1)),"")

That doesn't mean that is the correct place for it, but it was the only one which gave a result. I changed "" to "0", just so I could see what happened by putting the missing closing parenthesis in different places. It returned 0 in all places up until where it is above. If it is placed at the very end, it say too few arguments, so at least I know that is not the place for it.

So the code above does actually return a price, it is just unfortunately not the price of the correct match. If I copy it down a few more rows, I can see it is all over the place, with none of the prices matching the correct match and prices seeming to come from anywhere in column R.

Any thoughts as to what is not quite right in this formula?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,659
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Note that only the first part (index range) refers to a full column. Using entire columns for the other parts will result in very slow calculation times.
Changing the first reference to a limited range is causing the results to be offset from the criteria. The full column is used in the index range to prevent this, without it additional adjustments need to be made to the array which makes the formula less efficient.

With the formula that you have tried in post 14, you should find that the price being shown is from the row directly below the expected one.

I did have missing closing parentheses, which you have added in the correct place (I've forgotten the same one many times when I type the formula into the forum instead of excel :oops: ).
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
Changing the first reference to a limited range is causing the results to be offset from the criteria. The full column is used in the index range to prevent this, without it additional adjustments need to be made to the array which makes the formula less efficient.

With the formula that you have tried in post 14, you should find that the price being shown is from the row directly below the expected one.

I did have missing closing parentheses, which you have added in the correct place (I've forgotten the same one many times when I type the formula into the forum instead of excel :oops: ).
My bad on this one. I didn't realise I had altered the first part to numbered range rather than how you had it initially. Once I changed it back, it works perfectly.

Thanks a heap as that bails me out with a lot of saved time now, being able to very quickly import data from the required columns

cheers mate
 

honkin

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

ADVERTISEMENT

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")
A quick followup question on this one, if I may toadstool.

How would one change the results from being Yes or No, to being data in a particular column? So if A, B & C matched, then let's say what is in the corresponding cell in column AD

I tried it just replacing Yes with "$AD$2:$AD2", but this failed to achieve the desired result

cheers
 

Toadstool

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

This assumes you're OK with the first column AD result if there are multiple matches, otherwise we'll need to use multiple columns and a different formula to retrieve each match.

Honkin2.xlsx
ABCAD
1LeagueDate TimeMatchColumn AD
2A1-Jan-21 14:00Dog-CatAD2
3A1-Jan-21 14:00Dog-CatAD3
4A1-Jan-21 14:00Dog-CatAD4
5B1-Jan-21 14:00Dog-CatAD5
6B1-Feb-21 15:00Dog-CatAD6
7B1-Feb-21 15:00Dog-CatAD7
8B1-Feb-21 15:00Dog-CatAD8
9B1-Feb-21 15:00Cat-MouseAD9
10B1-Jan-21 14:00Dog-CatAD10
11B1-Jan-21 14:00Dog-CatAD11
12
Lay Over 2.5
Cell Formulas
RangeFormula
AD2:AD11AD2="AD"&ROW()


Honkin2.xlsx
ABCD
1LeagueDate TimeMatchMatch?
2A1-Jan-21 14:00Dog-CatAD2
3A1-Jan-21 14:00Dog-CatAD2
4A1-Jan-21 14:00Cat-MouseNo
5B1-Jan-21 14:00Cat-MouseNo
6B1-Feb-21 15:00Dog-CatAD6
7B1-Feb-21 18:00Dog-CatNo
8B1-Feb-21 15:00Cat-MouseAD9
9B1-Feb-21 18:00Cat-MouseNo
10B1-Jan-21 14:00Dog-CatAD5
11B1-Jan-21 14:00Cat-MouseNo
12B1-Jan-21 14:00Cat-MouseNo
13B1-Jan-21 14:00Cat-MouseNo
14B1-Jan-21 14:00Cat-MouseNo
15
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=IFERROR(INDEX('Lay Over 2.5'!$AD$2:$AD$9999,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")
 

honkin

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

This assumes you're OK with the first column AD result if there are multiple matches, otherwise we'll need to use multiple columns and a different formula to retrieve each match.

Honkin2.xlsx
ABCAD
1LeagueDate TimeMatchColumn AD
2A1-Jan-21 14:00Dog-CatAD2
3A1-Jan-21 14:00Dog-CatAD3
4A1-Jan-21 14:00Dog-CatAD4
5B1-Jan-21 14:00Dog-CatAD5
6B1-Feb-21 15:00Dog-CatAD6
7B1-Feb-21 15:00Dog-CatAD7
8B1-Feb-21 15:00Dog-CatAD8
9B1-Feb-21 15:00Cat-MouseAD9
10B1-Jan-21 14:00Dog-CatAD10
11B1-Jan-21 14:00Dog-CatAD11
12
Lay Over 2.5
Cell Formulas
RangeFormula
AD2:AD11AD2="AD"&ROW()


Honkin2.xlsx
ABCD
1LeagueDate TimeMatchMatch?
2A1-Jan-21 14:00Dog-CatAD2
3A1-Jan-21 14:00Dog-CatAD2
4A1-Jan-21 14:00Cat-MouseNo
5B1-Jan-21 14:00Cat-MouseNo
6B1-Feb-21 15:00Dog-CatAD6
7B1-Feb-21 18:00Dog-CatNo
8B1-Feb-21 15:00Cat-MouseAD9
9B1-Feb-21 18:00Cat-MouseNo
10B1-Jan-21 14:00Dog-CatAD5
11B1-Jan-21 14:00Cat-MouseNo
12B1-Jan-21 14:00Cat-MouseNo
13B1-Jan-21 14:00Cat-MouseNo
14B1-Jan-21 14:00Cat-MouseNo
15
Sheet2
Cell Formulas
RangeFormula
D2:D14D2=IFERROR(INDEX('Lay Over 2.5'!$AD$2:$AD$9999,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")
Cheers toadstool

That works really well, but for whatever reason, about 1/3 of the results come back as No. This is obviously a matching error and from what I can see, the date format varies in certain cells, but even if I highlight the entire AD column and change all the formats to the same, it still remains No.

The cells which worked all have the date showing in the formula bar like this - 02 Jan 2021 14:030. The ones which are No have it like this - 2/1/2021 15:00:00. As I said, I have highlighted the entire column and changed the formatting to Custom and dd mmm yyyy hh:mm, but the results remain the same. I have also tried changing the format to various options, but have had no success in eliminating that error.

Columns A & C are just formatted as General, so no issues there, so it really does appear to be column B which is the issue. Any thoughts on how to change the date format, not just in the cell, but in the formula bar? I even tried deleting what was in the cell and manually typing it in the correct format, but the formula bar still shows it in its original format, even though I have changed the format to what is required.

Any thoughts on rectifying this at all?

Thanks so much for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

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