Last 3 football results

[cmb80]

Hi all, in my attached example sheet you can see games in the Scottish premiership for 2014.

What I need to find is the last 3 results for teams (regardless of if they played home or away). The data is sorted chronologically.

In the example you can see cell H2 is Aberdeen. In I2 there most recent game was A (Away win), their previous game was H, and the game before that H.

I would like to do this without an array formula if possible due to the sheer amount of teams (this is just an example sheet of my larger file).

[Ali Kırksekiz]

For I2 cell

=INDEX($B$2:$E$200,SMALL(IF($B$2:$C$200=$H2,ROW($B$2:$B$200)-ROW($B$2)+1,ROW($B$200)+1),COUNTIF($B$2:$C$200,$H2)-(COLUMNS($I$2:I2)-1)),4)

This is an Array Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula.

Drag right (up to K2) then pull down.

[cmb80]

Hi and thanks, but as in my original post I want to avoid array (if possible) due to the amount of calculations I wish to do

[micope21]

Hi

Please see the file!

No Array formula

Regard