How get last n amount of entries from an evergrowing list

[Bah2]

I'm working on a statistical tool for analyzing hockey games for my friend, in which I list all the games and their results in a season vertically, each on it's own row (see attachment) home team and it's goals first, then visitor and result 1X2 wise on the end.

What would be the best way to get the results of the last 6 games (for example) for a certain team? Without using macros, since this should work both on Excel and OOCalc

[Ron Coderre]

(EDITED TO INCLUDE THE EDITED WORKBOOK)
Using your posted workbook...
N2: Team
O2: a team to analyze...Pelicans

and M3:S3 containing this list

Please Login or Register  to view this content.

and N4:N9 contains this list

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Copy M4 and paste into M5:M9

These formulas return the results you're looking for

Please Login or Register  to view this content.

Copy those formulas down through Row_9

With your sample data, these are the results:

Please Login or Register  to view this content.

Is that something you can work with?

[TheCman81]

Updated: Didn't see your request for individual teams

For this purpose put your team name in cell L12

Stats v2.xls

[Bah2]

Yeah, I think that's something I can start from.
Not exactly what I meant (forgot to mention that I need six last MATCHES from any given team, including aways),
but this is enough. All I really needed was a place to start, so I can work my way up and perhaps learn how to do it.

Cheers for the both of you.

[Ron Coderre]

See the proposed solution I posted...It lists the last 6 games the team participated in.

[TheCman81]

See the proposed solution I posted...It lists the last 6 games the team participated in.

I was only focusing on the results for the first column, didn't think about the away teams.

I liked your SEARCH function to lookup 2 arrays very handy.

[Bah2]

See the proposed solution I posted...It lists the last 6 games the team participated in.

Oh yeah, so it does. Had a too quick a glance at it, it seems.

[Bah2]

So yeah, that's definitely something I can work on or even use as is.
Much kudos.