Google Spreadsheets

[doulos]

I am certain that there are some statistically minded hockey fans out there that know how to do this so figured I would ask here first.

I am using Google Spreadsheets to track Stats from various leagues and I currently have a spreadsheet with a list of names of players and stats.

On a second sheet I want to create 'teams' of players that would pull their stats from the first Sheet.

For example on Sheet 2 I might have ...

Nugent-Hopkins, Ryan
Eberle, Jordan
Atkinson, Cam

On Sheet one I have all of their stats and I want to pull them into the second sheet based on the name.

How would I do this? I am sure it's easy enough but I hardly ever use spreadsheets in my current job.

[fantasyhockeygeek]

vlookup is your hookup!

=vlookup(cell containing the name of the player your want to look up the stats for, range of cells you want to search in with the player name being the first column, number of the column you want to be outputted, FALSE)

For example, you have the players on your "stat" sheet, called "stats".

• Their names are in column A from rows 2 through 100.
• GP is column B, P is column C, PIM is column D

On your "lookup sheet", you'd put the player name in column A. Say you're on row 2. For column B (where you want to output GP for the player in question) you'd input "=vlookup(A2, 'Stats'!$A$2:$D$100, 2, false)".

For column C, where you want P, it would be "=vlookup(A2, 'Stats'!$A$2:$D$100, 3, false)"
Hope that makes sense? Probably doesn't.

[doulos]

How did I know you would be the one to answer?!

I am going to go try this right now and see if I can get it to work. Will report back. Thanks!

[ericdaoust]

It may take some practice getting used to. I've been using vlookup for years and still stumble with it at times.

[doulos]

I may have hit a snag in that since I am using importxml to grab the data it doesn't have the correct values in my 'stats' page.

[DerekReese]

Originally Posted by doulos

I may have hit a snag in that since I am using importxml to grab the data it doesn't have the correct values in my 'stats' page.

You want to make sure all your "data" is in number form and not text form. I also find that if the names dont match exactly it wont work either.

[fantasyhockeygeek]

Sure, but that shouldn't impact your ability to figure out how to look them up. Just start with dummy stats to begin with, get the lookup right, then get the stats in there.

[doulos]

The syntax for the vlookup seems pretty straightforward but I think it won't work the way I have grabbed my data. I am using importxml to snag it from the AHL website for testing purposes, and so the value of the cells in the Stats sheet are not names, but are something else.

[ericdaoust]

Originally Posted by doulos

The syntax for the vlookup seems pretty straightforward but I think it won't work the way I have grabbed my data. I am using importxml to snag it from the AHL website for testing purposes, and so the value of the cells in the Stats sheet are not names, but are something else.

But isn't the player name included somewhere in the data that you've fetched?

[doulos]

Originally Posted by ericdaoust

But isn't the player name included somewhere in the data that you've fetched?

Well it 'displays' as Jordan Eberle, but when I click on the cell itself the value is =CONTINUE(A2, 3, 3) which I assume is because I am using =ImportXML to grab the data.

I never use spreadsheets so it's probably something silly...hahah.