#VALUE! error in Google Sheets but not in Excel

Hello.

I have copied an excel file over to Google Sheets and although it works pefectly in Excel, it throws up several #VALUE! errors in Sheets.
I hope someone can help me with this problem.

Thank you.

https://drive.google.com/file/d/1Yc3...ew?usp=sharing

I get ACCESS DENIED following the link in your posting.

I'm so sorry.

https://drive.google.com/file/d/1Yc3...ew?usp=sharing

Thank you.

Tom, you need to set the sharing options before getting the link to the sheet.

.

Apologies again. I think this is the one ...
https://drive.google.com/file/d/1Yc3...ew?usp=sharing

First, COUNT(...)>0=TRUE ? The =TRUE is superfluous.

The primary reason Excel and Google Sheets handle your formulas differently is that Excel doesn't require array formula entry for cols D and E formulas but Google Sheets does.

Enter just

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},MID(B2,FIND(" ",B2)+1,FIND(" ",B2,FIND(" ",B2)+2)-(FIND(" ",B2)+1))))

in L2, and Google Sheets returns 0. Change that to

=ArrayFormula(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},MID(B2,FIND(" ",B2)+1,FIND(" ",B2,FIND(" ",B2)+2)-(FIND(" ",B2)+1)))))

and Google Sheets returns 2. Rule-of-thumb: when trying to use the same formulas in Excel and Google Sheets, ALWAYS use array formula entry when any function call would return an array.

That said, you want to isolate the score substring, 2 strings of decimal numerals separated by a hyphen. Unless your teams would have hyphens in their names, find the hyphen, and find the rightmost space before the hyphen. The following works in Google Sheets.

D2:

Formula:

Please Login or Register  to view this content.

In Excel, that'd become the array formula =REPLACE(LEFT(B2,FIND(" ",B2,FIND("-",B2))),1,MATCH(1,0/(MID(B2,ROW(INDEX(A:A,1):INDEX(A:A,FIND("-",B2))),1)=" ")),"").

Fill D2 down, then copy the col D formulas and paste into col E. Unclear what the col F formulas are supposed to be.

Why not =REGEXEXTRACT(B2,"\d+\D+\d+") ? Also, FWIW, you can use unescaped - in character classes as long as it's the 1st character following [ or [^.

Thank you! I've used Haluk's solution but they both work equally well. One more thing, and in answer to your question about column F, it's used to show the points won for each prediction. 1 point is awarded for each correct RESULT and 3 points is awarded for each correct SCORE. The result is then shown in cell I16.
A formula for coloum F will be greatly appreciated.

Thanks again.

F2:

Formula:

Please Login or Register  to view this content.

Fill down as far as needed.

Thank you very much. I notice that all the matches that aren't a correct result or correct score are in red. Would it be possible to show all correct results in red, all correct scores in blue and all incorrect results/scores in black?

Thank you.

I didn't alter EXISTING conditional formatting. Here's a link to my working copy now with changed conditional formatting.