Vlookup? Index? Match?

[ronnie2113]

Hi Guys...

2 seconds after the first posts is yet another

I have created and excel sheet where I try to visualize some of the data collected from soccerstats.com!

But I have tried to pull all the played matches for Premier League and now I'd like to be able to pull the latest 4 home matches for the home team, but how do I do that from a table that has the same value multiple times AND it needs to take the data from the bottom and up?

Or do I have to do it another way?

Thanks, hope I can get some guidance!

[ShaunWhite]

I'm not sure about your specific example but it pays to create additional columns derived by comparing the current line to the prev/next lines.

This will let you do things like count the number of home games a team has played
eg
if this gametype <> prev gametype then
gamecount = 1
else
gamecount = previous row gamecount + 1

If everyone's played the same number of games then it's easy to find the rows where gamecount <= totgames - 4 (or 5 or 6 or whatever)

If everyone's played a different number of games, either maintain another column for it manually (nice and easy) or you can try to derive it using further columns:

one col to find the last home game (gamecount > next row game count) and store the gamecount

another col take that figure and store it on every row of the same game type.


Anyway that's not mean't to be an answer...just a tip to get you thinking if you've not already considered it.
Generally you need to break down exactly what steps you would take to do it manually, find a way to solve each step, make a column for it.

Good luck

[nigelk]

Have you got a column (date for example), so you can sort ascending/descending, that should take care of your bottom up data.

Post a sample of your sheet (unaltered) so we can have a look.

[Euler]

Really hard to comment without seeing the data. You could use find to match 'characters' within a cell to count iterations.