Can Excel arrays be used/indexed like traditional array/list structures?

0
votes

I'm tracking season results of the Premier League in an Excel spreadsheet and I would like to implement a way to show each team's form (the result of their last 5 games). This seems to be very difficult in Excel for some reason or another.

My idea is to create an array of all results from games involving the team, then display the last 5 items in the array elsewhere in the sheet. In python for example, I could index the array by doing array[-1], but I can't find any equivalent in Excel (doesn't help that "index" is a whole different function in Excel, making Googling this topic very difficult).

TL;DR is there an Excel equivalent (on Excel's arrays) for Python's array[-1]?

EDIT: Say I come up with a formula that results in the array {"Win", "Win", "Win", "Lose", "Draw", "Lose", "Win", "Draw"} (lets call this array 'form'), is there a way to get the last ("Draw"), second last ("Win"), third last ("Loss") etc. value from this array in Excel? Again using Python as an example, this would be done like form[-1], form[-2], form[-3] etc.

EDIT: Example Data

Say I want to look at Spurs' form for their last 2 games (let's pretend these 3 games are all they have played). My approach would be to use an array formula to store the 'Result' value in column I in an array from every row containing the value 'Spurs' (found in rows 10, 20, 33 for reference) (still working on the specifics of this part too). So then that array formula would evaluate to {"Spurs", "Draw", "Newcastle United"}. In a different cell I would then like to take the 3rd value of this array ("Newcastle United), and in the next cell I'd like the 2nd value of the array ("Draw") etc. etc.

1
excel
Better way to get help here to post sample sample data and expected output with your efforts. – Harun24HR
Done. Actual sample info is much too large and it really boils down to this problem so that is all I will present. I haven't made any significant efforts, all I want to know is if Excel arrays support indexing similar to traditional arrays. – 420fedoras
Many guys post a smaller version of the file in this situation. You'll need VBA to read the cells into an array that you can sort etc. You can use cells with a "sumif" for many scenarios as well. Storing Win / Loss / Draw in 3 separate arrays will allow you to easily get the 3 last loss (if any). Good luck. – William Walseth
Index has a row and column parameter. If you figure out the count of the values in your array, you can call these individually using these parameters. – JvdV
@420fedoras I am sure it can be done in excel. Just post few sample data may be 10 row of data then show expected output and explain logic to gain that output. – Harun24HR

1 Answers

0
votes

Given, as you wrote

enter image description here

=INDEX(Form,0,IF(1,N(ROW(INDIRECT(COUNTA(Form)-4&":"&COUNTA(Form))))))

will return an array of the last 5 entries in Form

If you want the last entry:

=INDEX(Form,0,COUNTA(Form))

next to last:

=INDEX(Form,0,COUNTA(Form)-1)

and so forth.