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

 

[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")

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

=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

 

[jasonb75]

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

=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]

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

=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]

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.

=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]

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 ).

 

[honkin]

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 ).

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]

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")

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

 

[jasonb75]

Just $AD2 should do it.

 

[Toadstool]

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
	A	B	C																											AD
1	League	Date Time	Match																											Column AD
2	A	1-Jan-21 14:00	Dog-Cat																											AD2
3	A	1-Jan-21 14:00	Dog-Cat																											AD3
4	A	1-Jan-21 14:00	Dog-Cat																											AD4
5	B	1-Jan-21 14:00	Dog-Cat																											AD5
6	B	1-Feb-21 15:00	Dog-Cat																											AD6
7	B	1-Feb-21 15:00	Dog-Cat																											AD7
8	B	1-Feb-21 15:00	Dog-Cat																											AD8
9	B	1-Feb-21 15:00	Cat-Mouse																											AD9
10	B	1-Jan-21 14:00	Dog-Cat																											AD10
11	B	1-Jan-21 14:00	Dog-Cat																											AD11
12
Lay Over 2.5

Cell Formulas
Range	Formula
AD2:AD11	AD2	="AD"&ROW()

Honkin2.xlsx
	A	B	C	D
1	League	Date Time	Match	Match?
2	A	1-Jan-21 14:00	Dog-Cat	AD2
3	A	1-Jan-21 14:00	Dog-Cat	AD2
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	AD6
7	B	1-Feb-21 18:00	Dog-Cat	No
8	B	1-Feb-21 15:00	Cat-Mouse	AD9
9	B	1-Feb-21 18:00	Cat-Mouse	No
10	B	1-Jan-21 14:00	Dog-Cat	AD5
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
15
Sheet2

Cell Formulas
Range	Formula
D2:D14	D2	=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]

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
	A	B	C																											AD
1	League	Date Time	Match																											Column AD
2	A	1-Jan-21 14:00	Dog-Cat																											AD2
3	A	1-Jan-21 14:00	Dog-Cat																											AD3
4	A	1-Jan-21 14:00	Dog-Cat																											AD4
5	B	1-Jan-21 14:00	Dog-Cat																											AD5
6	B	1-Feb-21 15:00	Dog-Cat																											AD6
7	B	1-Feb-21 15:00	Dog-Cat																											AD7
8	B	1-Feb-21 15:00	Dog-Cat																											AD8
9	B	1-Feb-21 15:00	Cat-Mouse																											AD9
10	B	1-Jan-21 14:00	Dog-Cat																											AD10
11	B	1-Jan-21 14:00	Dog-Cat																											AD11
12
Lay Over 2.5

Cell Formulas
Range	Formula
AD2:AD11	AD2	="AD"&ROW()

Honkin2.xlsx
	A	B	C	D
1	League	Date Time	Match	Match?
2	A	1-Jan-21 14:00	Dog-Cat	AD2
3	A	1-Jan-21 14:00	Dog-Cat	AD2
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	AD6
7	B	1-Feb-21 18:00	Dog-Cat	No
8	B	1-Feb-21 15:00	Cat-Mouse	AD9
9	B	1-Feb-21 18:00	Cat-Mouse	No
10	B	1-Jan-21 14:00	Dog-Cat	AD5
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
15
Sheet2

Cell Formulas
Range	Formula
D2:D14	D2	=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