New guy - Help with VLookup Formula!

[Cfiskey]

Hi guys,

Long time fantasty player who has recently decided to give BaseballHQ a try. A few days in, I am hooked!

I've always been a bit of a stats junkie and the analysis here takes it to a whole new level. I've typically been on Rotoworld which has been pretty good - but nowhere near the in depth analysis found here.

I'm battling for 1st in my one league and rebuilding in another.

I am trying to leverage the sorting and filtering described in the education section of the site. You can find it here. http://www.baseballhq.com/using-excel-downloading-data

I am good all the way through until I have to create the VLookup formula.

I have painstakingly gone through this about 3 times - slowly - and can't seem to nail it.

For one thing, the description of columns/rows in the instructions don't perfectly match the YTD pitcher and batter stats. I know enough about Excel to be dangerous, but for the life of me, I can't nail this down.

Does anybody have advice or even a template that works for them?

Thanks in advance.

Craig

[RAY@HQ]

Our stats files have probably changed slightly since we wrote those Excel tutorials. Can you post the exact formula you're trying to use? One of the Excel gurus here will likely be able to help. And if there's something that needs to be changed in the column, we can do that once we get the right answer.

[Michael@HQ]

Welcome to the forums too. You also can use Insert, Function from the Excel menu when you are using an unfamiliar or complicated function and it helps walk you through what to enter for each argument of the function.

[PD@HQ]

Just remember that in the formula =VLOOKUP(TERM or CELL,DATABASE or NAMED RANGE,NUMBER,FALSE), "NUMBER" is the number of columns to the right that you want to look up. So if the old articles about VLOOKUPs say to use column 9 to get HRs, that's because at the time, HRs were in the ninth column. If they are now in the 15th column, you have to use "15" as the count. If you want to VLOOKUP the HRs, you count leftward to the HR column, starting with the leftmost column as "1", not zero.

Also remember that the LOOKUP terms must be exactly the same, so "J.J. Hardy" is not the same as "JJ Hardy" (no periods) or "J.J.Hardy" (no space between "J.J." and "Hardy") or "J.J. Hardy " (space after "Hardy", and this one is sneaky and messes up a lot of VLOOKUP attempts).

Also, if the VLOOKUP is working in one column but not others, make sure the TERM is anchored in the leftmost column of the data range by using a "$" sign. =VLOOKUP($A2,STATSRANGE,4,FALSE) is not the same as =VLOOKUP(A2,STATSRANGE,4,FALSE) because as the formula is copied to the right, the TERM reference will change to B2, C2, D2 and so on.

Similarly, the search range needs to be anchored to prevent it moving as the formula is copied right and down. The search range $A$2:$Y$2 is not the same as A2:Y2.

I'm with Ray -- if you could tell us exactly which BHQ Excel file you're using and what you're trying to accomplish (including the formula you're using), it would help us help you.

[Cfiskey]

Hi guys,

Thanks for the support.

Basically I've walked through the instructions in the education section. I've created a workbook called 090212 (September 2, 2012). I've done the naming as described for consistency.

I have downloaded all of the YTD stats (as of the September 2) and have them all as their own tabs in the workbook. In my VLookup sheet I am working off of the AL Hitter tab and trying to pull info into my newly created VLookup tab. The first player I am trying to pull into it is Albert Pujols.

This is the function I am working off:
=VLOOKUP($A5,AL_H_090212,19,FALSE)

A couple things I have done:
1. Originally the far left column had a player identifier number. I deleted that.
2. I have changed the 13 to 19 to match the first HR column in AL Hitter tab (which I have renamed to match the script - AL_H_090212).
3. I tried removing the top row on the AL hitter page (it says player, player, player, all the way across)
4. I tried adding all the categories to my Vlookup sheet in row 5 as they are in the AL Hitter sheet in row 2.
5. I have tried variations of the players last name (including case sensitivity).

Current Status:I am close. When I click on cell 6C on my Vlookup sheet (this is under the header HR as per the instructions on the VLookup page) where the function is =VLOOKUP($A5,AL_H_090212,19,FALSE), I see this when I hit the Insert Function button at the top left in Excel:

Lookup_value $A5 = "Lastname"

Table_Array AL_H_090212 = {"Abreu", "Tony", 27, "B", 4, ...and so on}

Col_index_numb 19 = 19

Range_lookup = FALSE

In cell A6 I have the last name Pujols.
In cells A5 to G5 I have Lastname, BA, HR, RBI, SB, AB, H
In cell C6 I get an error #N/A (where the function is)

Note: My table array AL_H_090212 starts at Abreu in cell A3 and captures all stats in the entire sheet for all players.

Thanks guys.
Craig

[Cfiskey]

Yeesh, don't know why that came out so jumbled. It looked a lot neater and organized when I was writing it out.

I hope you can make sense of it.

[Michael@HQ]

Where you typed $A5 in your formula, it should be $A6 instead.

[Cfiskey]

Doh. I knew I was close.

Thanks michael! I appreciate it.

[Michael@HQ]

You're welcome.

You are mostly doing this for fun, right? I'm sure most of our subscribers are not doing VLOOKUPs but still get lots of value from BaseballHQ.com. The data is there if you want to use it, but that's not the most time-efficient way to use the website, in my opinion.

[PD@HQ]

You're welcome.

You are mostly doing this for fun, right? I'm sure most of our subscribers are not doing VLOOKUPs but still get lots of value from BaseballHQ.com. The data is there if you want to use it, but that's not the most time-efficient way to use the website, in my opinion.

But an excellent use of time to get better at using the concepts and figuring stuff out on your own. Also only my opinion, but I got started here by working the data on my own.