last 6 games form in soccer matches,suitable formula

[davide2574]

Hi

I am new of this forum, my english is not perfect. I am from italy
I have no solve and seeking in Google but I not understand how to apply the formula for last matches as you can to see the file.
I would like to learn. I think that the function is index and match?
Take care.

Davide

[davide2574]

don't know this formula?

[nathansav]

FORUM_D6.xlsxHI

Please find attached. I've had to add an extra column, to make populating the score easier, this is in column F. YOu dont need this, but without it, you'll stand little chance of understaing the formula.

Google:

Array Formulas
and
SMALL Excel Function

this will help understand. Basically, i am getting an array of the row numbers where the home team is the team name, so for example, rows 1, 2,5,6 then i am using small, which takes the x smallest value from an array so we are saying Small({1,2,5,6},x) where x increments each time to the right, and we are using this value for INDEX, which you're familar with.

[davide2574]

thank you for helping and also give the words to find in google. I would like to exctract as 3 points or W as you can see the colomun G and I instead the results?

[nathansav]

=INDEX($A$1:$F$300,SMALL((IF(($B$1:$B$300=$M3)*($D$1:$D$300<>"")*($E$1:$E$300<>""),ROW($B$1:$B$300))),2),6)

will need to be

=INDEX($A$1:$G$300,SMALL((IF(($B$1:$B$300=$M3)*($D$1:$D$300<>"")*($E$1:$E$300<>""),ROW($B$1:$B$300))),2),7)

[davide2574]

Hello nathansav

I have copy your suggestion of formula but give me the error NUM as you can to see the file. I maybe should ctrl+shift+enter?

[nathansav]

Hi,

Please find attached. I've error trapped it and think there were some that werent CTRL SHIFT + ENTERed.



FORUM_D12.xlsx

[davide2574]

Hallo Nath

I seeking to solve the problem for total and away with your formula of home but I have not solve
I have a hard head.
is right the formula for total last 6 matches:

=IF.ERROR(INDEX($A$1:$J$300;SMALL((IF(($B$1:$C$300=$M3)*($D$1:$D$300<>"")*($E$1:$E$300<>"");ROW($B$1:$C$300)));1);9);"")

the formula for away last 6 matches:

=IF.ERROR(INDEX($A$1:$J$300;SMALL((IF(($C$1:$C$300=$M3)*($E$1:$E$300<>"")*($D$1:$D$300<>"");ROW($C$1:$C$300)));1);9);"")

I would like to understand this small, if and index.
thank you very much for your very patient with me

[davide2574]

I attached the file , I have modify but I not understand why gives me the date .

[nathansav]

FORUM_FOOTBALL_1.xlsx

Hope this helps.