Football Box Pool - Help With Formula??

[olimits7]

Hello,

I created a football box pool in excel and I used a combination of the OFFSET and MATCH formulas to find out who the winner is in each quarter depending on what score I put in the box. The formula works fine with single digit numbers, but I get a #N/A error for double digit scores.

For double digit scores it should just look at the last digit in the score to determine who won that quarter. Does anybody know of a way that I can just look at the second digit??

Here is a copy of the formula I used:

=IF(OR(ISBLANK($C$15),ISBLANK($C$16)),"No Score",OFFSET($B$2,MATCH($C$16,$B$3:$B$12,0),MATCH($C$15,$C$2:$L$2,0)))

Thank you,

olimits7

 

[barry houdini]

I'd suggest that INDEX is a better function to use than OFFSET in this situation but, nevertheless, for your specific request, if I understand correctly, try replacing C15 in the formula with MOD(C15,10) and similar for C16, i.e.

=IF(COUNT(C15:C16)=2,INDEX(C3:L12,MATCH(MOD(C16,10),B3:B12,0),MATCH(MOD(C15,10),C2:L2,0)),"No Score")

 

[olimits7]

Thank you that worked out great.

olimits7