I just graduated! – The real world of VLOOKUPS

So you just graduated from college? Congratulations! If you’re like me, I hope you had a fun summer off! So now you decided it’s time to get serious. Time to start looking for your first real job. Scary stuff.

When I first started on my job search I had the most successful person I know (my millionaire uncle) look over my resume. I was looking for a job in a similar field (finance / accounting) but on the opposite coast. He told me right away, key words to use on your resume that will instantly get you recognized and out of that dreaded stack of college grads is *drum roll* EXCEL! He instructed me to learn three things right off the bat: VLOOKUPS, Pivot Tables, and Macros. He said “those are really easy to learn – do a quick google search – drop those in your resume or phone interview and you’ll stand out immediately”. Those words were like some foreign language to me at the time. Looking back, I wish I had an easy tutorial (like this one *nudge* *nudge* *wink* *wink* get the hint?) to tell me all I needed to know. If you haven’t guessed already, I’ll be covering the basics of VLOOKUPS in this blog post. (Part two will be macros – which is my personal favorite and current addiction – I’m an excel geek).

1. What is a VLOOKUP?

A VLOOKUP is a formula in excel. The excel definition of what it does is: “It looks for a value in the leftmost column of a table, and then returns the same row of a column you specify”. (That didn’t make much sense to me on my first time around so I’ll go into more detail and put my little twist on it).

2. But first … the format:

=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

That looks like a bunch of mumbo jumbo (i know!). So let’s define what you need to plug into this formula to make it work.

Variables:

a) =VLOOKUP – this always stays the same. It tells excel: “yo! we’re doing a vlookup foo!”

b) lookup_value – this is what you want to search for in the other table. (Essentially what a vlookup is doing in excel is it says “yo! sheet number 2! where is this “lookup_value” I’m looking for in your sheet?”)

c) table_array – this is where you want to search. (Once you’ve defined what you’re looking for, this is when you tell excel “yo, excel! dawg! we’re looking at sheet number (2,3,whatever name, even an outside workbook) for this value”).

d) column_index_num – this is what information you want excel to return for you once it’s found the “lookup_value”. (This is really the magical and important stuff. Excel now knows what you’re looking for, and it knows where to look for it, but now you need to tell it what you want to know! Do you want excel to tell you what’s in the second column to the right of that value?, 3rd?, 100th? That’s the powerful step right there.)

e) [range_lookup] – there are two options for this either “1” or “0”. “0” means an exact match and “1” means an approximate match. I cannot tell you that I have ever used 1. I always use 0. I want an exact match.

Now that you know what each variable means let’s do an EXAMPLE:

*side note: vlookups are used when you have so much data it would take an eternity to search for it all one by one, but this is just an example so it’s a tiny bit smaller*

THE SCOOP: Let’s say you have this workbook set up : Sheet 1:

  A B C
1 Username Customer Name Quantity Sold
2 krispy Krispy Kreme
3 dunkin Dunkin Donuts
4 glazed Dunkin Donuts
5 sprinkles A Lotta Donuts
6 munchkins Wowsa Donuts

Sheet 2:

  A B
1 Username Quantity Sold
2 dunkin 501
3 glazed 1007
4 sprinkles 254
5 munchkin 36
6 krispy 2500

THE PROBLEM: So let’s say your boss asks you to find out how many donuts were sold by the username “sprinkles”. Of course, your response would be, “I can use a Vlookup!” and he/she would be impressed!. This is what you would need to do.

1. Define your variables (just like any math problem!)

a) = VLOOKUP – this never changes (easy peasy!)

b)  lookup_value – what is it again he/she wanted you to look up? oh yeah! “sprinkles” (or you could select the cell where the information is located that you want to search – in this case “A5“)

c) table_array – where are we looking for the information we want? in this case it is Sheet2!A:B

d) column_index_num – what column is the information we want to find located? – in this case it is column(the boss said he wanted to know the “quantity” – which is located in column number 2)

e) [range_lookup] – this is usually “0

So the magical VLOOKUP formula we would want to type into excel’s cell C5 is:

=VLOOKUP(A5,Sheet2!A:B,2,0)

And Boom! This will magically return the value associated with “sprinkles” which is the number 254!

Now that you’re a VLOOKUP pro here’s some little tips and tricks:

*You don’t have to type all that! All you really have to type is “VLOOKUP”, then click the cell you are searching for (A5), select the sheet and area you are searching for the previously defined value (Sheet2!A:B), and then type the column number of where the information you are looking for in that other sheet is located, and then “0”. Easy peasy!

*You can drag vlookups down a column. This will magically fill out the same information for all the other values.

I hope this is helpful! I tried to make it easy and fun to follow along. Since I know my first time around with these formulas they seemed like a foreign language of some sort. Once you get to using them on a daily basis, however, you realize how truly powerful they are! The most useful, and magical little formula: the VLOOKUP!

To summarize, if an interviewer (or boss, or anyone, your mom?) asks you how proficient you are at excel. You would now be able to drop that you know how to use VLOOKUPS.

Now, say they respond, “What is a VLOOKUP?”. Or, “When would you use one?”. Your response would be, “A VLOOKUP is an excel formula. It is most useful when you have a large amount of data you need to search through in multiple sheets or workbooks. A VLOOKUP takes the value you are searching for, searches the sheet or workbook you instruct it to search through, and returns the corresponding column value.”

Now I’ll set you free to go play with VLOOKUPS. Trust me, you will use them on a daily basis and they are awesome. (I’ve been working for 3 years now in accounting / finance and I still use them!)

I would love to hear what you guys would like to learn about and of course stay tuned for the next episode: MACROS!

Advertisements

One thought on “I just graduated! – The real world of VLOOKUPS

  1. Pingback: INDEX – MATCH : VLOOKUP’s powerful brother | excelwizards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s