HQ NOW: Experts Leagues Winner's Circle, 2023Using Excel: VLOOKUPs
by Patrick Davitt
Click here to return to the Education page.
In our first two installments, we laid the foundation for Excel use when we discussed the basics of moving BHQ data to your computer and into Excel and naming protocols for files, tabs and data ranges.
Now we move on to the good stuff -— how to use the BHQ data to do analysis. In particular, we'll be using one of Excel's most powerful formulas: the VLOOKUP.
The VLOOKUP formula performs exactly what you'd expect from the latter part of the name -— it "looks up" the data associated with a particular entry in a database.
Here's how it works: The formula syntax is: VLOOKUP(Value,Database,Column,Rule). Let's define these terms:
- "Value" is the term being looked up. In our case, it will be a player's name.
- "Database" is the collection of data we are going to be looking into. The values being looked up (the player name) must be the leftmost column of the database. It is good practice to also be sure this is the "A" column. The database can be defined in two ways: by manually clicking-and-dragging over the database area (remember to hit the F4 function key to set the values as absolute), or by referring to its name, as discussed in the last article. Of the two, using the name is far better.
- "Column" is the number of the column within the database in which the particular value will be found. The column with the lookup values (player names) is column number 1, with the other columns numbering up to the right.
- "Rule" is a "True/False" value describing whether the lookup should use approximate values or exact values. Oddly, "false" refers to the exact-match rule, and we will be using it exclusively. Make "false" your default for all your Excel BHQ work.
EXERCISE
Okay, do 10 sit-ups.
Just kidding -— not that kind of exercise. Let's use a concrete example. If you haven't already, download the March 18 files (or substitute your own date for 0318 below). Aggregate and name them as described in the last article. In particular, make sure you name the AL Hitters tab and data as AL_H_031812. That's the one we'll be using for this example.
Now insert a worksheet using the Insert menu. Name the new sheet "Worksheet" or "VLOOKUPs" or some similarly descriptive name. In cell A5, type in "Hitter" (it's usually a good idea when you're just starting to build a sheet to leave a few rows open at the top as you might need them). Moving across to the right, type in "BA", "HR", "RBI", "SB", "AB" and "H" in cells B5-G5 respectively.
In the hitter column, type in the names of your team's hitters. If you aren't in an AL league, just pick some at random. Make sure you type the names exactly as they appear in the BHQ database: last name-comma (no space)-first name. Capital letters don't matter, but it's good to get into the habit of typing the names exactly the same. (We'll show you a neat trick to make this foolproof later on.)
With your players listed, you're ready to do your first VLOOKUP. In cell C5, the first one under "HR", enter:
=VLOOKUP($A5,AL_H_031812,13,FALSE)
Then hit "Enter." Going through it in detail, the terms in this formula mean:
- $A5: The cell with the player name in it, with the "$" used to lock the reference into the "A" column;
- AL_H_031812: The range of data we are looking into, with the player names in the leftmost column;
- 13: The "HR" column is the 13th column in the database, with the player name column being the first; and
FALSE: Means that we require exact matches to the player names, not approximations or near-matches.
If you've set it up correctly, the projected HR total for this player will appear in the cell. Go check the data to confirm.
If the result is correct, the next step is to copy the formula through the other category columns. Copy cell C5 into D5-G5. The HR total will appear in all those cells. So you'll have to go through each cell and replace the column reference "13" with 14 for RBI, 17 for SB, 8 for AB, and 10 for H.
Now you should see the correct projections for each stat category for the player named in cell A5. All that's left is to copy the row of formulas into the cells aligned with the other players in your player list (ie cells C6:G6 for the player in A6, C12:G12 for the player in A12, etc). Then you can total them or manipulate them any way you like. Try totaling them.
Note that we did not use VLOOKUP to capture the BA results, although we could have. Instead, when dealing with ratio categories, it's usually advantageous to write in formulas that figure those out. For instance, in cell B5, if you enter the formula...
=G5/F5
...you will create the BA for the player in A5. Copy the formula down into the rows with the other players in them, and ultimately into the row with the totals, to get your squad's aggregate BA.
Once you get the hitters data set up, try doing the pitchers. If you find yourself having trouble or not able to get something to work, use the Excel help screens or post a message in the "HQ Help" forum.
Some inexperienced Excel users will build player and team sheets by laboriously copying-and-pasting each player's stat row from the data table into a new sheet. You should see that VLOOKUP is a much neater and faster way to accomplish this.
Besides a straight player sheet as described, you could build a basic trade evaluator using VLOOKUPs to call up data lines for comparing players, or totaling players by stat categories and/or dollar values.
As you get more proficient, you can build out workbooks that combine your current league stats with the projected totals for all league players to get a good idea how the league will shape up over the balance of a season. In these books, trade evaluation can show you how your teams will move in the projected final standings after any deal.
Best of all, such ongoing books can be built with VLOOKUP references to a named database (HQ_Hitters), and thereby easily updated by simply overwriting the data in that database with more current data.
NEXT: Sorting & Filtering
