I Know Why You’re Not Happy (You Haven’t Been Exposed to VLookups)

So my friend came to me this weekend, after having a wonderful Thanksgiving with his family in Virginia, and he was looking like this:

Now some may think possibly:

1. He ate too much
2. He had not been around me in a full week
3. He loves his momma a lot and misses her

But wait I thought.  Does this guy know what a VLookup is?  Maybe that would totally cheer him up.  I started conversating, “Sachin, I think you really need to learn about VLookups, it’s totally changed my life, for the better obviously, and my soccer model that you think is mediocrely cool would not operate without them.”

“Jack, please tell me more!”

“Sachin, I will tell you more.  What if I told you instead of keeping all of those girls’ names, numbers, eye colors (kind of creepy), and other random information in a spreadsheet, that must be at least 300 rows, you could type in a girl’s name once and all that vital information would populate for you.  Would that be something that interests you?  At the top of the page, with you having to do absolutely no scrolling, you could type in a name and all that creepy information you’ve collected would generate for you without having to fumble through your massive table.”

“Yes, that’s exactly what I need in my life!”

“Well sit back my friend, lets get you a good setup.”

What is a VLookup?  A VLookup, or vertical lookup, is an Excel function and is a convenient way to grab data from a table by attempting to match a given value looking down a column, or vertically.  If you wanted to go against conventional practice you could also investigate looking at HLookups, or a horizontal lookup, but that may be too much craziness for one day.

What are the parameters that go into a VLookup function?

• lookup_value is the value one wants to lookup!  It’s the value in the leftmost column in the table you want to do your lookup.  Perhaps, for Sachin, his table has the nicknames of girls as they appear in his cell phone.  After we set this up all he’ll have to do is type that one nickname and then voila!
• table_array is the range of cells that are being investigated.  It’s Sachin’s whole table, including all his rows, starting with nicknames, and accompanying columns with random information.
• col_index_num is the column of the table that he wants to retrieve with this VLookup.  The lookup column is considered 1, not 0 as some may think.  If this were for instance set to 3 then this lookup would:
  • Go down the first column until the lookup_value was found
  • Count over two more columns and retrieve that value
• [range_lookup] is an optional variable.  It’s asking if searching down the column should be an exact match or just an approximate match.  If one wants an exact match one will set this variable to FALSE, which is what I will use in this example and is what I use about 95% of the time.  If one leaves this blank it will default to FALSE.  Approximate matching, or setting this variable to TRUE, can be useful if you have a lookup number that may or may not be one of the numbers in the column but you want to lookup a similar number.  The tricky part about the approximate match is the lookup column numbers in the table need to be in ascending order.  I encourage you to play with this setting later on.

Before we get too crazy with Sachin’s table let’s look at the following example, a 7×2 table with numbers in the leftmost column and colors in the second, and try to answer the following:

1. What is the value being looked up?
2. What is the value that will be returned?
3. What would happen if col_index_num was set to 2 instead of 1?
4. What would happen if col_index_num was set to 10 instead of 1?

Answers:

1. 2.  This function will look down column C and look for a 2.
2. It’s not very exciting but it will return a value of 2, which is the same as our lookup value.  This is not all that practical but it starts to paint the picture for what this function can do.
3. The function would return Brown.  It would look for the row that has a 2 in the leftmost column and then go to the second column of the table and return that value.
4. You can’t do that!  And Excel will let you know by returning a #REF! error.  The REF is short for reference and tells us we need to check our reference to some of our values we’re using.  Our table only has two columns but we’re asking to send us back the 10th column in the table.  We’d either have to adjust our table or adjust the col_index_num to eliminate that error.

Let’s now start looking at Sachin’s table that he’s started constructing with some VLookups setup at the top:

Here Sachin has started constructing his table.  He says he has many more names to add but I’m starting to question the validity of this list as many of the last names are eerily resembling my fantasy football team players:)

But you’ll see now he can just type in a nickname, how he originally typed it into his phone, in the green C2 cell.  Row 2 is a bunch of VLookups that look through the table and bring back a specific column of information relating to that lookup value.  In our original VLookup example the col_index_num value was an integer but we’ve made that value a dynamic value that can change.  The VLookup formula in E2 is actually looking to E1 to get its value for col_index_num.

This may be easier to see by looking at a different lookup cell and with a different lookup_value.  We’re now looking for information relating to “Uber Girl”:

You’ll notice D2 through I2 update with new information as C2 gets changed.  While this is a small glimpse into the power of the VLookup I hope you can start seeing the use if you had 100’s or 1000’s of rows in a table with many more columns, and maybe more productive (some would say) information.

Sachin is well on his way to living that good life and is now looking like this:

While the fun and games are temporarily over it doesn’t have to last long.  If you sign up for my email list I’ll send you a copy of “The Bet is Round” for free in pdf form, where I map my crazy adventure into betting on the 2018 FIFA World Cup.  I’d love to hear your feedback.

 

“Nothing ventured, nothing gained”