The VLOOKUP function in MS Excel

My brother-in-law, Tom, asked me about the VLOOKUP function in Excel, which is something I've used regularly over the past 15 years or so, so I thought I'd put together a little explanation of its purpose and invocation for him and anyone else who might find it useful.

VLOOKUP is essentially a database function, used to get a value for one table from another table when the two tables have a foreign key/unique key relationship.  In other words, you have a bunch of rows in one table that reference a single row in another.  Let me give you an example that I've used in preparing for fantasy football pretty much every year.

In one table, I have a list of NFL quarterbacks.  I know each QBs name and what team he plays on, and perhaps some of his stats from past seasons.

Now I need to know what week each of these guys has a bye so that I don't end up drafting two QBs who are both off on the same week.  I happen to have another table that lists which is each team's bye week in the upcoming season.

Now I could just go through the list of players and type the appropriate bye week into each row, but since there are actually 169 QBs that would be a lot of busy work.  In fact, there are also 476 wide receivers on my list, so I've got to find another way or I will certainly go mad.

Here's where the VLOOKUP function swoops in to save me.  As you can see, in my first (player) table, I have the team that each player plays on.  In some cases, a team appears more than once in that table, as there are multiple players per team.  For example, Charlie Batch, Byron Leftwich and Ben Roethlisberger all play for the Pittsburgh Steelers (or PIT).  In this table, the team code (e.g. CAR or PIT) is what's referred to in database speak as a "foreign key".  A foreign key value can appear multiple times and is a match for a unique value in another table.  So the "Team" column in my player table is the foreign key, and it refers to the "primary" or "unique" key column also called "Team" in my second (byes) table.

Here's how I use VLOOKUP to give me the bye week for each player:

Like with any function in Excel, you start by typing "=" followed by the function name and an opening parenthesis.  Once you get there, you get a prompt telling you the argument names that the function takes.  The first is the "lookup_value", the foreign key that I mentioned earlier.  For the example row, the value is in cell B2, "PIT".

So I enter B2, and move on to the "table_array" argument.  This is the table from which I want to draw my value.  Since I need to find out what bye week PIT will have, this will be the table that includes "PIT" as a unique key value and the corresponding bye week.

As you can see, I'm providing "Byes!A:B" as the argument.  That simply means the "Byes" worksheet, columns A through B.  I selected the entire columns rather than A2:B33, because if I do the latter, when I copy my formula into the rest of the quarterbacks' cells later, Excel will increment each of those numeric values as I copy further and further down the sheet, which will cause errors.  It thinks it's being smart.  You can get around that with A$2:B$33 (which means use absolute rows 2-33), but it's just quicker for me to select the two entire columns and be done with it.

Next I'll need to enter my "col_index_num".  This is just the column number that I want to return the value from, counting from the left.  So in our byes table, column 1 is the "Team" column and column 2 is the "Bye" column.  We want the bye week, so we'll enter 2.

Finally, we enter the "range_lookup" argument.  It defaults to TRUE, which in my experience is worthless.  We want FALSE, which means an exact match only please.  This way "PIT" will not match "PHI".  I have no idea how Excel determines approximate matches, but it doesn't seem at all useful to me.  Maybe someone will read this who can enlighten me.

So now we close the parentheses and we're done with our VLOOKUP formula for determining Charlie Batch's bye week.

As you can see, he's off in week 5.  Was that easier than looking up Pittsburgh's bye week myself and typing it into that cell?  No.  But you'll see where the big time savings come in in a moment.  First I want to point out the little green corner of that cell.  Excel is warning me that there are empty cells in the VLOOKUP table because I selected the entire columns and not just the cells with values in them (remember A2:B33?).  Don't worry about it.  It doesn't matter here.

Now I can simply copy the cell (C2 in my players table), highlight cells C3 through C40+, and paste.

Finally, and not strictly relevant to the discussion, you'll probably want to copy all the cells that have the formula in them and replace them with their resultant values so that you don't accidentally replace them when your moving things around further, and so you're not constantly recalculating them either.  Just highlight the whole lot of them, right-click, select "Copy", right-click again, select "Paste Special...", and choose "Values".

[Edit] - Jen pointed out, and rightly, that this copy/paste operation makes sense for my case because the data in the lookup table won't change.  If your lookup table involves frequent edits, it might be better to keep the formulas, since they'll automatically update with any changes in the source table that way. [/Edit]

OK, and you're done.  We now have the bye week for each of the thousand or so skill position players in the NFL, and in a lot less time than manually looking up and entering each value would have been.  Let me know if the comments below if you have any questions or I missed anything.