Syntax Help Please

[Gunslinger380]

Hi all,


If someone could please help me with the proper formula I would grateful.

here is the situation. for my fantasy league I have in column A the season dates. In column B I have the teams, and in Column C I have wins and losses. I am having some trouble finding the correct formulas that will look up the last three dates from today and pull the results for the team. note: I am looking for three separate formulas i.e. one for the last result, one for the second oldest result, and one for the third oldest.

I am also looking for something that will give me the next three future dates for a team. i.e the next game from today and so on.

I have a decent general knowledge of the functions and entering them into formulas I am having just a bit of trouble putting it all together.


Thanks in advance

 

[NiMip]

EDIT: for some reason, the MAX formulas are refusing to be posted in full. They're the same as the MIN formulas, but replace MIN with MAX, replace > with < and make sure the cell references are shifted up to the F5-F7 cells
<today())*($b$2:$b$40=$f$2),$a$2:$a$40))
<today())*($b$2:$b$40=$f$2),$a$2:$a$40))


I think this might do what you're after. You have to press CONTROL+SHIFT+ENTER for these formulas... {} should appear around the formulas if done properly.

	A	B	C	D	E	F	G
1
2	25/05/2017	abc	9/6		Select Team:	xyz
3	1/06/2017	xyz	3/6
4	8/06/2017	whatever	6/6			Date	Score
5	15/06/2017	abc	7/3		Last result:
6	22/06/2017	xyz	10/6		Second Last Result:
7	29/06/2017	whatever	5/2		Third Last Result:
8	6/07/2017	xyz	8/3
9	13/07/2017	whatever	9/8		Next Game Date:
10	20/07/2017	xyz	3/5		Two Games:
11	27/07/2017	whatever	7/9		Three Games:
12	...	...	...

<tbody>
</tbody>

<today())*($b$2:$b$40=$f$2),$a$2:$a$40))
<f5) *($b$2:$b$40="$F$2),$A$2:$A$40))
<today())*($b$2:$b$40=$f$2),$a$2:$a$40))
<today())*($b$2:$b$40=$f$2),$a$2:$a$40))
F6: =MAX(IF(($A$2:$A$40<F5)*($B$2:$B$40=$F$2),$A$2:$A$40))
<f5) *($b$2:$b$40=" $f$2),$a$2:$a$40))
<f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
<f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
<f6)*($b$2:$b$40=$f$2),$a$2:$a$40))
F5: =MAX(IF(($A$2:$A$40<today())*($b$2:$b$40=$f$2),$a$2:$a$40))
<f5) *($b$2:$b$40="$F$2),$A$2:$A$40))
F6: =MAX(IF(($A$2:$A$40<F5)*($B$2:$B$40=$F$2),$A$2:$A$40))
<f5) *($b$2:$b$40=" $f$2),$a$2:$a$40))
<f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
<f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
F7: Fill down from F6<f6)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f6)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)></today())*($b$2:$b$40=$f$2),$a$2:$a$40))
</f6)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)*($b$2:$b$40=$f$2),$a$2:$a$40))
</f5)>

G5: =INDEX($C$2:$C$40,MATCH(F5,IF($B$2:$B$40=$F$2,$A$2:$A$40),0))
G6: Fill from G5
G7: Fill from G6

F9: =MIN(IF(($A$2:$A$40>TODAY())*($B$2:$B$40=$F$2),$A$2:$A$40))
F10: =MIN(IF(($A$2:$A$40>F9)*($B$2:$B$40=$F$2),$A$2:$A$40))
F11: </today())*($b$2:$b$40=$f$2),$a$2:$a$40))
Fill down from F10</today())*($b$2:$b$40=$f$2),$a$2:$a$40))
</today())*($b$2:$b$40=$f$2),$a$2:$a$40))

 

[Gunslinger380]

Yes! thank you very much! the last three were what I was looking for. I can flip MIN and MAX around to get the desired date then use SUMIFS to put the data where I need it. This is exactly what I needed, I was just a little uncertain about the array formula.

Thanks again!!!