Spreadsheet: 171723d1343836138-help-with-combining-lookup-and-countif-formulas-stats1
Sheet: Calc Sheet

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | A |  | B |  |  | C | D | E
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Last 3 Games Average v Each Other |  | Last 6 games goal average |  | A / B Av. | Pro soccer % of over 2.5 goals | Lay 0-0 | [NAME] Under 1.5 market
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Consecutive Goals |  | Consecutive Goals Scored |  | Consecutive Clean Sheets
 |  |  |  | Games |  | Team |  |  |  |  | Games |  | Team |  | C/S
Item | Date | League | KO | 1st | 2nd | Scored | Conceded | Home |  | Away | 1st | 2nd | Scored | Conceded |  |  |  |  |  |  |  |  |  | HT | 2nd | Scored | Conceeded |  | Over 1.5 Goals | 1st Half Goal |  | 2nd Half Goal | Result |  |  | Total Goals |  | 0 | 1 | 2 | 3 | 4 | 5 | 5+
 |  |  |  | LINES BELOW COLOUR CODED SO TO RESULTS ON GAMES SHEET
1 | 2012-04-15 00:00:00 | SPA D1 | 11:00:00 | 3 | 2 | 3 | 3 | Arsenal | v | Man United | 1 | 1 | 1 | 1 | 0 | 14 |  | 17 | 2.833333333333333 | 1.416666666666667 | 0.72 |  |  | 4 | 3 | 4 | 4 | #N/A | Yes | Yes | 1 | Yes | 4 | - | 0 | 4
 | RH to Populate from daily fixtures | RH to Populate from daily fixtures | RH to Populate from daily fixtures | This formula needs to look up how many consecutive games Arsenal have scored in the first half. In date order. The calculation will look cell i7 and then look for Arsenal on the Games Workbook and review columns C & G for goals. Once it hits a zero the calculation needs to stop. The answer in case should be 3 | This formula needs to look up how many consecutive games Arsenal have scored in the second half. In date order. The calculation will look cell i7 and then look for Arsenal on the Games Workbook and review columns D & I for goals. Once it hits a zero the calculation needs to stop. The answer in case should be 2 | This formula needs to look up how many consecutive games Arsenal have scored in. In date order. The calculation will look cell i7 and then look for Arsenal on the Games Workbook and review columns E & J for goals. Once it hits a zero the calculation needs to stop. The answer in case should be 3 | This formula needs to look up how many consecutive games Arsenal have conceeded. In date order. The calculation will look cell i7 and then look for Arsenal on the Games Workbook and review columns E & J for goals. Once it hits a zero the calculation needs to stop. The answer in case should be 3 |  |  |  | This needs to look up how many consecutive games the away team have score in the first half, so when it comes to a zero it stops counting | This needs to look up how many consecutive games the away team have score in the second half, so when it comes to a zero it stops counting | This needs to look up how many concecutive games the away team have scored in, so when it comes to a zero it stops couning | This needs to look up how many concecutive games the away team have Conceeded in, so when it comes to a zero it stops counting | This needs to look up how consecutive clean sheets the away team have kept, so when it comes to a value greater than 0 it stops counting. | This needs to look up the last 3 times the teams (Cell I7 & K7) have met and return the total number of goals scored on those 3 games, column K. For Arsenal v Man United answer should be 14 |  | This needs to look up the teams in Column I7 & K7 and then search the last 6 times they have played and return the number of goals they have scored, column E & J. Answer 17 |  | Calculation | Information from another website |  |  | Calculation |  |  |  |  | Updated When Results come through