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
 

Toadstool

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

That works really well, but for whatever reason, about 1/3 of the results come back as No. ...
On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.
Yes, as soon as I copied a cell in B from the source and pasted it as Values, the result turned to a price. A painful way to do it, but have no control over the sheets downloaded from the data supplier, so it will have to do

Thanks again
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
Just $AD2 should do it.
Cheers Jason. Apologies for the late reply. I missed your answer initially. No, that didn't work, but Toadstool came back with a response which helped. Ta!
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
On the data I provided? Yes, that's because there's no match for columns A, B and C. If it's on your data then either install XL2BB or copy and paste as text the cells from both sheets.

Changing the format of a cell doesn't change its content so the formula results will be unchanged.
OK, have finally installed Xl2bb to try and resolve this correctly as it is working in some cells and not working in others.

The source sheet is Predictology Result File and the columns in both source and target are the same; D, E, F, G, H, I, J & K

In all but H, the formula works a treat. I simple change the first column reference to the new column and all is good. Columns D and H in the source are scores, so they are presented like this; 1-0, 0-0, 1-2 etc I thought this may have been the culprit, but since D has no such issues, that seems unlikely.

So, initially the scores were input manually and often when typing 1-2, it might change the cell to 1 Feb instead of 1-2. I would simply highlight the cell and F1 and change the format to Custom and @ and then type 1-2 again and it would be fine.

So as you can see, the formula in H just sits there with the formula visible, rather than actually doing anything. Even a No would be a better response, as it would indicate no match in A, B & C, but doing nothing has me scratching me head

I hope something in there strikes you as not right, as it is doing my head in

cheers

Predictology_Trading Template v3.1.xlsm
DEFGHIJK
41-1112=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")134
50-0000=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")213
61-0101=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")202
71-1112=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")112
80-3033=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")134
90-2022=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")022
100-0000=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")000
110-1011=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")022
121-0101=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")101
132-0202=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")202
140-2022=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")123
150-2022=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")033
161-0101=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")123
171-0101=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")112
181-0101=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")101
192-0202=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")404
Lay Over 2.5
Cell Formulas
RangeFormula
D4:D19D4=IFERROR(INDEX('Predictology Result File'!$D$2:$D$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
E4:E19E4=IFERROR(INDEX('Predictology Result File'!$E$2:$E$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
F4:F19F4=IFERROR(INDEX('Predictology Result File'!$F$2:$F$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
G4:G19G4=IFERROR(INDEX('Predictology Result File'!$G$2:$G$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
I4:I19I4=IFERROR(INDEX('Predictology Result File'!$I$2:$I$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
J4:J19J4=IFERROR(INDEX('Predictology Result File'!$J$2:$J$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
K4:K19K4=IFERROR(INDEX('Predictology Result File'!$K$2:$K$9999,MATCH(1,INDEX((A4='Predictology Result File'!$A$2:$A$9999)*(B4='Predictology Result File'!$B$2:$B$9999)*(C4='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
 

Toadstool

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

ADVERTISEMENT

OK, have finally installed Xl2bb to try and resolve this correctly as it is working in some cells and not working in others.

The source sheet is Predictology Result File and the columns in both source and target are the same; D, E, F, G, H, I, J & K...
You're only showing a part of your calculation sheet and none of the other sheet so it's not possible to provide a complete solution.

Your column H was formatted as Text so didn't interpret what you entered as a formula. Format column H as General then delete all of H2 down contents. Put this in H2 and copy down:
Excel Formula:
=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
 
Solution

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
You're only showing a part of your calculation sheet and none of the other sheet so it's not possible to provide a complete solution.

Your column H was formatted as Text so didn't interpret what you entered as a formula. Format column H as General then delete all of H2 down contents. Put this in H2 and copy down:
Excel Formula:
=IFERROR(INDEX('Predictology Result File'!$H$2:$H$9999,MATCH(1,INDEX((A2='Predictology Result File'!$A$2:$A$9999)*(B2='Predictology Result File'!$B$2:$B$9999)*(C2='Predictology Result File'!$C$2:$C$9999),0,1),0)),"No")
Cheers Toadstool and thanks for your reply.

Initially that row was formatted as Custom I believe, so I actually just tried a few different formats to see if I could get a result. I even tried General, but not with deleting the contents and then adding the formula. The formula was copied first, then I tried different formats.

That is working nicely now, so thanks very much

cheers
 

honkin

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

ADVERTISEMENT

You obviously have vastly more experience in excel than I, so I wonder if I might pick pick your brain for a sec on the subject of the initial post about automating the copying process of results.

The sheet this went into is just 1 of about 10 in a central workbook. 1 sheet shows all the matches and their results and the subsequent sheets are all various systems, so only have the selections for that system. The aim is to try and reduce the workload by having all the results populate right through the workbook as soon as the results are pasted in, but the problem arises with things being deleted, like abandoned matches etc.

So a file is downloaded daily and I generally go a couple of days either side of today's date to catch those matches which might be very early morning GMT. The macros for each system are run on this downloaded file, with the result being copied into the central file and each of the relevant tabs. It is this sheet I have been working on, trying to in some way automate a way for the results across all sheets when a single paste of results occurs in D, E, F, G, H, I, J & K.

When things are deleted, like abandoned or postponed matches and the like, what is the best way to not lose the coding in those columns, or is it simply a matter of manually copying the code back into any cells which need it? I tried protecting each sheet and only leaving the match details and score columns unlocked, but I was then unable to delete those matches I referred to.

Is there any tried and tested method for setting up this kind of automated sheet?

Thanks in advance mate
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
I'm not sure I'm following you, examples are always best, but if I understand you're pasting columns of data into a sheet but that sheet also has columns of formulae.

If you lock the formulae columns then what's to stop you selecting the data columns, pressing the Delete key, then paste in the data columns?
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
225
Office Version
  1. 2016
Platform
  1. MacOS
I'm not sure I'm following you, examples are always best, but if I understand you're pasting columns of data into a sheet but that sheet also has columns of formulae.

If you lock the formulae columns then what's to stop you selecting the data columns, pressing the Delete key, then paste in the data columns?
Apologies for the delay, Toadstool.

This was the way I had already tried, but my issue with it is the amount of matches which are either postponed, cancelled or abandoned worldwide. Columns A, B & C are the League, Date/Time and Match details. The next 8 columns are the score details, Half Time Score, Half Time Home Goals, Half Time Away Goals, Half Time Total Goals, Full Time Score, Full Time Home Goals, Full Time Away Goals, Full Time Total Goals. The next half a dozen columns or so are other match stats which require no formula, with the last handful of columns handling a count of the number of matches, number of wins, strike rate and P&L. To lock the 8 score columns and the last few columns means that if matches are deleted, I am unable to select that row to delete the match. In fact I am unable to delete the row at all, even if I select allow user to delete rows.

I had hoped to make it a locked workbook, as you suggested, and be able to remove matches easily if required, but it seems the best way forward is to simply add the scores in the central sheet and when adding new matches to the individual model sheets, simply copy and paste to remove any matches which did not take place. So if a match in row 200 was postponed, I highlight from 201 down and copy and paste it to row 200. If I actually delete the row, I end up with the #REF! in each cell which had a formula, which would require copying and pasting the formula down again.

It's not perfect, but seems to work for now.

Thanks again for your help and suggestions
 

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