Combine COUNTIF, OFFSET and SUM in GoogleSheets

[ErnestFichtner]

Hi i have a few questions, i try to make advanced formula but still without succes, so i decide to post here.
At first im looking for a data in different TAB which i will write a formula.

Searched Tab is 'Match History'
Result Tab is 'Players'
What have i done?
Counting the number of occurrences (for me is number of played matches for player) for example =COUNTIF('Match History'!A:BK,"player") and this is correctly returns 541.
What i want?
1. Finding all occurrences of a given player counting all records occuring two to the rifhr of the found records, summing then and dividing by their number.
2. Finding the last occurrence of a given player and getting from this row (row) the values from the timestamp field (timestamp is first record in a rows).
3. Finding all occurrences of a given player and counting the number in the last month.
4. Finding all occurrences of a given player and counting the number in the last year.

Thanks for any help.

 

[ErnestFichtner]

I made a solution for point 2 its working but looks like this..
=INDEKS('Match History'!A:A,ArrayFormula(MAX(MAX(('Match History'!B:B=B4)*(WIERSZ('Match History'!B:B))),MAX(('Match History'!F:F=B4)*(WIERSZ('Match History'!F:F))),MAX(('Match History'!J:J=B4)*(WIERSZ('Match History'!J:J))),MAX(('Match History'!N:N=B4)*(WIERSZ('Match History'!N:N))),MAX(('Match History'!R:R=B4)*(WIERSZ('Match History'!R:R))),MAX(('Match History'!V:V=B4)*(WIERSZ('Match History'!V:V))),MAX(('Match History'!Z:Z=B4)*(WIERSZ('Match History'!Z:Z))),MAX(('Match History'!AD:AD=B4)*(WIERSZ('Match History'!AD:AD))),MAX(('Match History'!AH:AH=B4)*(WIERSZ('Match History'!AH:AH))),MAX(('Match History'!AL:AL=B4)*(WIERSZ('Match History'!AL:AL))),MAX(('Match History'!AV:AV=B4)*(WIERSZ('Match History'!AV:AV))),MAX(('Match History'!AZ:AZ=B4)*(WIERSZ('Match History'!AZ:AZ))),MAX(('Match History'!BD:BD=B4)*(WIERSZ('Match History'!BD:BD))),MAX(('Match History'!BH:BH=B4)*(WIERSZ('Match History'!BH:BH))))),1)

 

[ErnestFichtner]

in ENGLISH:
=INDEX('Match History'!A:A,ArrayFormula(MAX(MAX(('Match History'!B:B=B4)*(ROW('Match History'!B:B))),MAX(('Match History'!F:F=B4)*(ROW('Match History'!F:F))),MAX(('Match History'!J:J=B4)*(ROW('Match History'!J:J))),MAX(('Match History'!N:N=B4)*(ROW('Match History'!N:N))),MAX(('Match History'!R:R=B4)*(ROW('Match History'!R:R))),MAX(('Match History'!V:V=B4)*(ROW('Match History'!V:V))),MAX(('Match History'!Z:Z=B4)*(ROW('Match History'!Z:Z))),MAX(('Match History'!AD:AD=B4)*(ROW('Match History'!AD:AD))),MAX(('Match History'!AH:AH=B4)*(ROW('Match History'!AH:AH))),MAX(('Match History'!AL:AL=B4)*(ROW('Match History'!AL:AL))),MAX(('Match History'!AV:AV=B4)*(ROW('Match History'!AV:AV))),MAX(('Match History'!AZ:AZ=B4)*(ROW('Match History'!AZ:AZ))),MAX(('Match History'!BD:BD=B4)*(ROW('Match History'!BD:BD))),MAX(('Match History'!BH:BH=B4)*(ROW('Match History'!BH:BH))))),1)