Extract last 6 results for a specific football team

[Crafted06]

[FONT=&quot]Hello.[/FONT]
[FONT=&quot]I have on the 1st sheet the entire results of the Premier league 2018-2019 fixtures.
They are set up as a table, from column A to D.
Column A = Home team
Column B = Home team score
Column C = Away team score
Column D = Away team
This table has the purpose to be dynamic, as i periodically add at the bottom, the new fixture results.

[/FONT]
[FONT=&quot]I want to extract them on the 2nd sheet in the exact form, but in 3 different tables (for a specific team):
Last 6 results at Home
Last 6 results away
Last 6 results overall
This also needs to be dynamic. As i only want to input data on the 1st sheet.

[/FONT]
[FONT=&quot]I've tried different formulas to extract this, but I didn't manage to do it .
I need help on what formulas should i use and how?[/FONT]

 

[steve the fish]

Put a team name in say cell G2 then these:

Home:

=IFERROR(INDEX(A$2:A$1000,LARGE(IF($A$2:$A$1000=$G$2,ROW($A$2:$A$1000)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Away:

=IFERROR(INDEX(A$2:A$1000,LARGE(IF($D$2:$D$1000=$G$2,ROW($D$2:$D$1000)-ROW($D$2)+1),ROWS($A$1:A1))),"")

Either:

=IFERROR(INDEX(A$2:A$1000,LARGE(IF(($A$2:$A$1000=$G$2)+($D$2:$D$1000=$G$2),ROW($D$2:$D$1000)-ROW($D$2)+1),ROWS($A$1:A1))),"")

All need entry via CTRL-SHIFT-ENTER not just enter or they wont work. Drag across and down. It assumes the scores start in A2 and ive used row 1000 as the last row to test. If you have more data than that change it to a larger more appropriate number.

 

[Crafted06]

It works.

Thank you!