INDEX/MATCH function against another cell on same spreadsheet

Hi all,

Can someone please help me with a potential IF/MATCH function?

I have a ‘ score result’ in my spreadsheet (see B1).

I want to calculate the points awarded for predictions. So in cell C3, a prediction of 1-2 should be like this: 1 (matching 1 in cell B1 = 5 points, 2 (matching 2 in cell B1) = 5 points.

Can anyone please help me write a suitable formula?

Thank you.

You may have formatted them to look like football scores... but your 1-2 values are defaulting to dates. Do you REALLY need to have the 1 and 2 in the same cell? It would be soooo much simpler to have them in separate cells.

What is the score for NOT predicting the H/A score correctly?

Hi Glenn,

Yes excel was being a bit funny, so I was just uploading this to get the ideas and then I`ll work on the formating.

Ideally I don't want in seperate cells, because it may end up taking more time from me to split predicted scores every week into seperate cells.


0 points for not predicting the scores correctly (I`ll be calculating other stuff seperate i.e was a win, draw or loss predicted)

Thank you!

In d3:

=IF(LEFT(C3,SEARCH("-",C3)-1)=LEFT($B$1,SEARCH("-",$B$1)-1),5,0)+IF(MID(C3,SEARCH("-",C3)+1,255)=MID($B$1,SEARCH("-",$B$1)+1,255),5,0)

copied down.

As opposed to this (separate columns):
=IF(G3=G$1,5,0)+IF(H3=H$1,5,0)

and separating the scores couldn't be easier:

=--TRIM(MID(SUBSTITUTE("-"&$B11,"-",REPT(" ",125)),125*COLUMNS($A:A),125))

copied across and down.

see sheet. You will regret NOT separating them... sooner or later.

Thank you so much Glenn. I`ll take your words on advice on board. For now this solution works! rep added. thanks again!