Help with Index Match formula finding last 4 occurences of the name

[dangorka]

Hi guys,

I have a simple Excel sheet which contains data in two sheets (basically a football league schedule)

Data is contained in column A (home team) and column B (away team for each match)

In column O I manually put the name of the team and formula has to find last four times when following team played a game (no matter if they were home or away team) and match the value from column C from last occurence being listed in cell O2, one before last occurence in cell O3 etc.

I attached the spreadsheet with values which must be found by formula in range O2:O5

Any idea how to achieve that with formulas?
Thanks

cross-posted at mrexcel.com/forum/excel-questions/1110298-help-index-match-formula-finding-last-4-occurences-name.html

[Olly]

In O2, enter:

Please Login or Register  to view this content.

...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. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy to O3:O5.