Calculate Result of Last N Games

Hello,

First time poster here so please let me know if I am posting this in the wrong forum or not following any established rules!

I have a spreadsheet that gives me the result of each NBA game played so far this season as well as who has taken the first shot/made the first basket/won the tipoff, etc. What I am looking to create is a formula for who has made the first basket for each team for the last 7 games that the first basket was scored by each team, but am looking to do this without using an array formula or VB as the original file is extremely large and the array will bog it down and I do not know how to use VB.

Looking at tab 'SCS', the list is in order of the date the games were played. Column C shows the away team, column E, the home team. If the player from the away team scored the first basket of the game, it would show in column N and for the home team, column O. Columns A-J are raw data pasted daily and columns K-T are normally formulas (pasted as values here to create a smaller file).

On tab 'FBP', there are several pivot tables and columns with formulas (all hidden). The result section I am looking for is highlighted orange

What I am trying to create is a non-array formula in the orange highlighted cells that would give me the last 7 players that made a basket for the team (ignoring games that the first basket was not made for the team). Currently they are hardcoded to show the expected result. On tab 'FBP', the table on the right was put in to help explain what I am looking for: the green highlighted cells is the team I am looking at and their most recent games (also referenced in column Z); the blue highlights are when a player from that team hit the first basket, and the purple shows the date on which they hit. Again, the only formula I am looking to create is in the orange cells to show the 7 most recent players to hit the first shot; 1 (Column AE) being the most recent and 7 (Column AK) being the 7th most recent. I would like the formula to pull from the 'SCS' tab instead of the table, the table is there just as a reference.

Please note that the table will be updated as more games are played so the formula would need to be relatively dynamic to include new lines being added daily. Please let me know if there in any other information that is needed or if I am being unclear. I have spent 8+ total hours trying out formulas, watching YouTube videos, and reading through articles to no avail, so hopefully someone can help me!

Had to do it with arrays if you want formula: =CONCAT(INDEX(SCS!$N$2:$N$534,MATCH(1,(SCS!$A$2:$A$534=$AE$4)*(SCS!$C$2:$C$534=FBP!$Z$5),),1)&IFNA(INDEX(SCS!$O$2:$O$534,MATCH(1,(SCS!$A$2:$A$534=FBP!$AE$4)*(SCS!$E$2:$E$534=FBP!$Z$5),),1),"")). Give that a go. Its late here and im running out of steam but it seems to work

This works, thank you, but is not exactly what I am looking for. I guess I did not mention that I only put the purple highlighted cells (cells AE4:AK4) above the result area as an example to show the dates that a player from that team made the first basket to help in my explanation. Normally they will not be there and I would like to reference cells AE2:AK2 as the (1) most recent game that a player from that team made the first basket to the (7) 7th most recent game that a player from that team made the first basket. The purple highlighted cells and the table to the right are only used to aid in the understanding of what I am trying to accomplish. Let me know if that doesn't make sense.