(EDITED TO INCLUDE THE EDITED WORKBOOK)
Using your posted workbook...
N2: Team
O2: a team to analyze...Pelicans
and M3:S3 containing this list
and N4:N9 contains this list
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
begins the list of the last 6 rows that contain the team name
Copy M4 and paste into M5:M9
These formulas return the results you're looking for
Copy those formulas down through Row_9
With your sample data, these are the results:
Is that something you can work with?
Bookmarks