Let me come clean. I screwed up. And it likely will cause you to see errors in your spreadsheets. That’s the whole reason for this post.
What Happened?
While this post is going to address a very important topic (resolving VLOOKUP errors), there wasn’t much of a need for this until I came up with a new format for the Player ID Map. The intent was to make the Player ID Map easily updatable. I hate having to lookup the IDs, birth dates, and handedness of all the new players.
And it’s always bothered me that there was no easy way for you to get updated Player ID information.
Let’s be honest. It’s a pain in the ass. Especially this time of year when players are switching teams every day and minor league players we haven’t had to deal with in the past are now projected to reach the big leagues this season. It’s tedious to keep teams up-to-date and to add these new players.
I needed to find a way to improve this process and to make everyone’s lives a little easier.
The Solution
The solution was to make the Player ID Map available in an online CSV file. One you connect that online file to your Excel spreadsheet, you simply have to right-click on the Player ID Map and hit “Refresh”. You will instantly get any update I’ve made.
Sounds amazing, right?
The Problem
The fly in the ointment happens to be the way Fangraphs structures their player IDs. Major leaguers, like Jose Abreu, have a purely numeric ID. Whereas minor leaguers that have not reach the big leagues, like Yoan Moncada, have the text “sa” in front of a string of numbers.
The unintended consequence of importing the Player ID Map file is that because some IDs contain text, Excel will treat the ENTIRE imported column as text.
The problem is that reports you download from Fangraphs and then open in Excel treat the player ID column as numeric values.
Warning… It’s About to Get Technical
If you’re fine with the old Player ID Map and the fact that it doesn’t get updated very often, you don’t have to use the new one. The old one can be downloaded here and will still be updated periodically. You can stop reading this post and save yourself some sanity.
But if a little complication doesn’t scare you off and you see the value in being able to refresh the Player ID Map and get regular updates… Keep reading.
Text and Numbers Are Treated Differently
Excel and most other computer applications treat text and numbers differently. And this is a common problem with VLOOKUPS. So the number “15676” is not the same as a text string of “15676”. So in our VLOOKUPS, we need to make sure we are comparing numbers to numbers and text to text.
Consider the Error Message
The first step in resolving a VLOOKUP problem is to understand the error message you’re seeing.
The “#N/A” error is the most common VLOOKUP error. And it essentially means that a match was not found during the lookup.
There are two main reasons a match would not be found:
- The item (player ID) doesn’t exist where you told Excel to look for it
- Or you told Excel to look for the wrong data type (look for a text value in a list of numbers, or vice versa)
You can easily test the first error by manually performing the search yourself. Let’s walk through a hypothetical example with Jose Abreu. He’s a well known player. He’ll surely be in the Steamer projections I’ve downloaded.
I see from the data that Abreu’s Fangraphs ID is 15676. If I trace that through into the Steamer Hitter projections, I am able to locate Abreu. So why isn’t the VLOOKUP finding the same match?
