VLOOKUPs are great, unless you’re struggling to get them to work, then they’re a massive pain in the backside – there are a lot of tutorials on how to get them right, but most of them already assume you’re more than a beginner at using Excel or similar spreadsheet software like Google’s Spreadsheet or OpenOffice Calc. This guide is only going to assume a very basic knowledge of excel and by the end will help you get stuck right into it!
For those of you who don’t know this is roughly what a VLOOKUP looks like:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
And for those of you who aren’t sure what that means, this might help.
VLOOKUP([cell you want to look up], [where you want to look up against], [which corresponding data you want to use], [match type])
And for those of you who still aren’t sure what it is you need to do to get this working, I wrote this blog post especially for you!
Lets look at the above formula in all of it’s parts – whilst I’ve described it as best I can, it helps to have a few examples!
Example VLOOKUP
Before that, lets set a hypothetical dataset which we want to do a simple lookup with. In the first spreadsheet we have a list of names and house numbers, in the next spreadsheet we have the list of names, but this time with date of birth and gender.
Lets consider that we want to have all the information in one sheet, this is something that VLOOKUPs can do really well – especially as it means that you don’t have to copy and paste all the data from one to the other (especially useful if they’re not in the same order!).
=vlookup([cell you want to look up], [where you want to look up against], [which corresponding column you want to return], [match type])
The first part (or argument) is the cell which contains the first value you want to find – so for this example it would be the first cell in the “name” column, this is because it will be the common element in the VLOOKUP. So when the formula is run, it will look for the contents of this cell in the other sheet.
If we assume that we have headings in the data the formula will start =VLOOKUP(A2
=vlookup([cell you want to look up], [where you want to look up against], [which corresponding column you want to return], [match type])
Next, you choose where Excel is going to look for this data to lookup against. Remember, that not only does it have to have the corresponding data which we’ve specified in the first step, but also it needs to have the contents of the other fields you want to include too.
So, whilst editing the formula switch to the other sheet and select the whole data area by clicking and dragging – or you can enter in the values in yourself if you know what they are.
Now the formula should look like this =VLOOKUP(A2, ‘Sheet 2’! A2:B5,
=vlookup([cell you want to look up], [where you want to look up against], [which corresponding column you want to return], [match type])
So, whilst we’ve selected what is the value you want to lookup and where excel will look for it, next, we need to see which information we want returned – we enter in the column number with the relevant data.
In this example, the “name” is located in the first column and then the “DOB” is in the second one. This means that the number we’d then add would be “2”. This can be kind of confusing to start with as the columns are laballed with letters rather than numbers – but then again, it’s easy when you know how!
Nearly there
The formula, nearly finished, reads like so =VLOOKUP(A2, ‘Sheet 2’! A2:B5, 2,
=vlookup([cell you want to look up], [where you want to look up against], [which corresponding column you want to return], [match type])
Finally we specify the last part, which is a 0 (false) or a 1 (true), which represents whether or not we want an exact match (0) or a partial one (1). Partial matches can be useful, but in this case, if you have similar entries it may return the wrong one.
The completed formula then will look like so =VLOOKUP(A2, ‘Sheet 2’! A2:B5, 2,0)
What you should now have is the Date of Birth which corresponds to the name, along with the address – on the same spreadsheet. To apply this to the rest of the data you can click and drag the cell down the column and it will lookup the values in column A for you.
Something you will notice, however, is that the bottom of the list may start returning errors (#N/A). This is because when you drag the formula, not only does it complete the formula for the first column (A-B-C-D-E etc), but it also does this for array we’ve selected on Sheet 2 – which means you start to lookup against the wrong data.
You just need to add the dollar symbol ($) in front of the range you don’t want to change. so this way your formula will then look like this =VLOOKUP(A2, ‘Sheet 2’! $A2:$B5, 2,0)
Now you can click and drag the formula and the array won’t change, meaning you get the right values back.
And you’re done!
This is the most simple application of a VLOOKUP, there are otherways you can use them, also in addition to HLOOKUPs (the horizontal variety) and all sorts of other formulas which make your data much easier to work with.
I hope that was easy enough for you, if you have any other questions about VLOOKUPs – then ask away in the comments below.