Excel Like a Champ pt. 4: How to Use the Trick of VLookup
Hello and welcome back, my beautiful and intelligent excel geniuses!!! I hope everyone has been doing amazing since the last time we were together (and I hope you have been practicing and brushing up on your excel skills of course!) I am back today with yet another segment on how to make you use excel like a consumer research pro!
I want to open with an excel joke today (bare with me friends): A pivot table walks into a bar and says, “Put me in the same tab, will ya?”
Yes- I know that was incredibly lame, but I warned you to be bare with me.
Today, I want to teach you guys about the VLookup tool, and how that can help you transport data into another sheet or different workbook entirely within seconds. In order to set this up, we need to start a new sheet on the same workbook we have been using with our sample data. To do this, at the bottom of the excel sheet, you should see where it says,”Sheet1,” and then a + sign next to that. Hit the + sign and it will add a new sheet called “Sheet2.” You can change this name if you want by clicking on the tab.
To set this up, I want to give us labels to work with. We are going to be pulling the artist data, so A2 should be Artist 1, A3 should be Artist 2, and A4 should be Artist 3. In B1, I want the label Average, because that is the data we are going to be pulling for this specific example. Once this is all set up, we are ready to go. Before we actually begin, I want to explain some context on what we are doing. Say that you have a large spreadsheet of data with 100s of artists and their song sales and other inferential statistics. Say you want to use this data to look at other factors or complete some different analysis on it, or to even just clean up the visual look- you would probably want to work on a different sheet. VLookup is the tool that can help us input that data within seconds. Now, you might be thinking, “Gosh Zoe, haven’t you heard of copy and paste?” And you would of course be correct. However, the reason we use VLookup is because it links the data, so that if on the original sheet something changes, it will change on the new sheet as well. This really just ensures accuracy and efficiency when transferring data.
Now we are going to get started actually using the VLookup tool. To do this, on Sheet2, I want you to click in cell B2, the blank after Artist 1. We are going to write a formula in order to extract this information from the other sheet. In order to do this, we are going to need a lookup value, a table array, and a column index number. All of those terms sound confusing, but basically we are setting up the parameters for how to get the numbers extracted from the original sheet.
So in cell B2, type in =VLOOKUP. They have a preset box where you can just input the specific parameters, but I personally like just writing out the formula myself. So they first thing in the formula is what we are actually looking for, in this case, it’s the value for Artist 1, which is cell A2. Therefore, A2 is the first part of the formula in parenthesis. So far we have:
=VLOOKIP (A2
Next, we need to clarify we are looking for information from a different worksheet, aka Sheet1, so we need to add that after a comma (this is apart of the table array information in the formula). In order to lock Sheet 1 in, we need to put in the formula with an exclamation point so it looks like this:
=VLOOKUP(A2, Sheet1!,
We must also include the range of data in the table array that it is looking in. If we go back to Sheet1, we see that need the formula to consider the data from the A column to the H column, where the average is. We need to indicate the exact range is from A2 until H4 in the formula. We also need to look this in by using $ signs between the letters. This is what it will look like:
=VLOOKUP(A2, Sheet1!$A$2:$H$4,
The last step in the formula is to specific what actual column the information we are looking for is in, and we do this with a number. In this practice case, we are looking for the average. On Sheet1, that is the 8th column, so we use the number 8. This is what the final formula is going to look like:
=VLOOKUP(A2, Sheet1!$A$2:$H$4,8)
If you plug that formula into Sheet2 in cell B2, the numerical value of 12.4 will pop up. You can then drag that formula down (it will automatically change the lookup value), and then within seconds, you have all of the data pulled from the other sheet.
This Vlookup tool comes in handy for a lot of different situations, so it is good to know how to write the formula. Of course, I encourage you to play around with this tool on your own so you get use to writing the complex formula, and so you can understand what situations this would be useful for.
That is all I have for today. As always, I love you all.
Until next time, future excel champs,
Your favorite excel teacher, Zoe
