The vlookup function is a ‘gateway drug’ so to speak in writing excel formulas. People either get it to work, or they don’t. If they can get it to work, they will move on to more advanced excel functions, and go on to create some elaborate worksheets. If they can’t get this to work, it often leads to the user just giving up on formulas all together and forgoing the amazing automation Microsoft Excel has to offer.
In that spirit, I really want you to understand the VLookup, and so the information below will help in that mission.
Some Info on the Look-Up
VLookup is built-in function in Microsoft Excel used to find a specific value or string and return a corresponding value or string based on the location you specify. This function only works for data that is organized in a way that allows the data to be found. In basic terms, you use this function to find something in another part of your Excel workbook.
The two most common look-up formulas in Microsoft Excel are the VLookup and HLookup. The HLookup being the second most common, and VLookup being the most common.
The “V” in VLookup stands for VERTICAL, while the “H” in HLookup stands for HORIZONTAL. This difference between the two is simply how you plan to look up that item. If you are going to search across the columns, then you need a VLookup. Down the rows? Then you need a HLookup.
The format of the data in your sheet is the #1 killer for first time Vlookup-ers.
The above two cells of data are not equal. One is formatted as a number (which you would expect), and the other is formatted as text (noted by the arrow in the corner). If you try to find a number, in a column of text, or vice versa, your VLookup will not work. This often leads to people just giving up on this function and admitting defeat.
It’s extremely important before you begin writing your VLookup formula’s that you ensure all numbers that should be numbers are, and all text that should be text, is.
Typically, the function you will use most often is VLookup, and while the below example is EXTREMELY simple, it’s basic format will work across any VLookup you wish to perform.
So let’s first start with a data set. We have Names(A), Age(B) and the person’s Location(C).
Then in a different location in the worksheet we have Name(G) and what we wish to look up, Location(H).
In Cell H2 is where we will put the formula. You can simply click on the cell and type in the formula bar, or click into the cell and begin typing the formula. If the cell already has something in it, like another formula, double-clicking will put you in the cell and ready to write.
The formula for a VLookup is easy to write, and after a few times of having to write them, should be easy enough to remember forever.
Let’s take a look at each component of the formula so we understand exactly what we’re looking at.
Every formula begins with the equals-sign (=), and since we are using the VLookup function, the formula begins with “VLOOKUP”.
Next, it’s time to pass the function some parameters, and you do this inside of the opened ” ( ” and closed ” ) ” parenthesis and separate each parameter by a comma ” , “.
The first parameter is the cell you wish to find. In this case, the cell with the item (Text “John” in this case) we are trying to find is G2.
The next parameter is the range of cells where we will be looking, starting with the first column where we can find “John”, and ending where what we wish to return (Location) can be found, this is represented by A:C (in words A to C).
Before Moving On…
The above parameter in Step 4 can sometimes be tricky to understand.
Your range doesn’t always have to start with “A”, and it doesn’t have to be in the format of columns(A:C, B:C, C:D). Your range could be “B:C”, or a range of cells like”B5:C10″, just for example.
The key to choosing how you do this lies with two questions you need to ask yourself:
1. Where is the data located that you plan to look in?
2. How do you plan to use this formula once it’s complete?
If the data set you need to look up in includes data you DO NOT want included in your returns, then you can use a specific range.
For example, take the below data set you are working with…
Your data is organized as it should, but you notice in column M there are two “Johns”. In a typical VLookup scenario, if you looked up in the entire data set using columns (M:O), looking up “Country” would return the first “John” it finds, which in this case is “United States”.
But let’s say you didn’t care for North America, but just wanted to see returns for South America. For this, you could use a range of cells. Instead of the columns (M:O), you would use (M4:O6). This would limit the returns of your lookup to JUST South America, and a VLookup for “John” would return “Brasil”.
The second consideration is how you plan to use this formula once it’s complete. If you are just using this VLookup in a form of some sort, or just as a “one time” tool to find something, it won’t really matter all that much whether you use the columns (M:O) or range(M1:O6) as long as you are including all data in your results.
But if you plan to drag the formula vertically or horizontally, the cell reference does matter and you need to include another step.
Locking Cell References
You may have seen some formulas in another person’s workbook with funny symbols that look like this, $A:$V or $A1:B1. The ‘dollar-sign’ in this case locks the cell reference, and it’s placement is important.
You can either add the symbols manually using the SHIFT+4 key on your keyboard, or using the function key F4 on your keyboard. Hitting F4 multiple times will toggle through the locking scenarios.
For the example of using columns, locking the cell reference is simple – either ‘Off’ (A:V) or ‘On’ ($A:$V). But for specific ranges (A1:B1), you have a few ways of locking this reference to choose from. For each Cell, you can lock either the Row, or the Column.
Meaning, $A$1 locks the reference to A1, and no amount of dragging either vertically or horizontally will change that. However, $A1 will only lock the column on “A”, and A$1 will only lock the row on “1”.
Let’s explore this a little further to ensure it makes sense…
Rundown of the Locking Scenarios
Locking neither the Row or Column: (A1)
If you were to drag a formula with the reference ‘A1’ in it horizontally, the column would change (A1, B1, C1, D1…), if you were to drag it vertically, the row would change (A1, A2, A3, A4, A5…)
Locking both the Row and Column: ($A$1)
If you were to drag a formula with $A$1 in it either horizontally or vertically, the reference in that formula would not change, and always be $A$1.
Locking the Row and NOT the Column: (A$1)
If you were to drag a formula with A$1 in it horizontally, the column would change(B$1, C$1, D$1…), if you dragged it vertically, nothing would change (A$1).
Locking the Column and NOT the Row: ($A1)
If you were drag a formula with $A1 in it horizontally, nothing would change ($A1), if you dragged it vertically, the row would change ($A1, $A2, $A3…)
The next parameter is a number, this number represents the number of the column in the set of columns we told the formula to look in(A:C) where we will find what we wish to return (Johns Location).
To say it differently, if we passed this formula the parameter of A:C and what we were looking for was in column C, the number would be 3. If we had passed the parameter A:Z and what we were looking for was in column Z, the number would be 26.
It’s important to pass the right parameter here because if you meant to find something in column C, but only put the number 2, you’d actually be returning whatever is in column B. ALSO, if you put in a number larger than your column set (you passed A:C, but then used the number 4), your formula would fail and show an error.
The last parameter you pass to this formula is either the word ‘TRUE’ or ‘FALSE’. A lot of online tutorials will just glare over this step simply because they don’t feel like it’s important to discuss, or maybe don’t want to get into the specifics of what’s happening, but I think it’s important to explain. The majority of the time you use the VLookup function, you will be using the word ‘FALSE’ at the end of it, but it’s important to know why.
This last parameter is simply the answer to the question “Can we look for something CLOSE to what you need, instead of an EXACT match?” And the answer to this question is generally FALSE, meaning, no, find the exact match. By entering ‘TRUE’ in this parameter, you’re giving excel the ability to find something as close to what you’re looking for as possible.
For example, if what you’re looking for is “John” but there is no “John” in your data set, there may, however, be a “Johnny”, by using ‘TRUE’ for this parameter, you’re giving excel the choice to return for “Johnny”. But I hate to use this right off the bat, because then I assume the formula found “John” when in fact, it actually found “Johnny”.
Using ‘TRUE’ to show gaps in data
Generally speaking, when you perform a VLookup, you want an exact match. But looking for a close-match is not a bad thing – I tend to pass the ‘TRUE’ parameter when I’m making a second pass on my VLookup on things that have not been found, and I will often return the 1st column so that it shows me what it HAS found instead of my exact match. This just serves as an audit on the data to show me where gaps may exist.
The Final Product
The final product is above, and is a basic VLookup formula anyone can use. Note the formula in the formula bar, and the return for the VLookup of “John” returns “United States”.