What is a vlookup?
A vlookup is a formula used in Excel, which allows you to check a range of data with a second range of data. For the vlookup to work each range of data must have a field where the data matches. This may not sound too useful at first. However, the vlookup in Excel is extremely powerful and can save you hours of manual work.
How to do a vlookup in Excel
Take a look at the picture below, which shows two separate ranges of data in an Excel worksheet. These ranges of data do not need to be in the same worksheet for a vlookup to work. You can have the ranges of data in separate worksheets or even separate Excel workbooks altogether. However, it’s usually wise not to do a vlookup from one Excel file to another because if anyone moves one of the files or changes a folder name where the file is stored it can break the vlookup. I have kept the vlookup in the same worksheet to show how the vlookup works.
In the above spread sheet you’ll see a list of people who have made advanced payments to attend a party. The price is 15.00 and most people have paid the full amount. However, you will see some people have only paid a deposit, which means the remainder will need to be paid after the party. It’s also possible people will attend the party without paying in advance.
This is a great example of when you could use a vlookup to provide you with information on who has paid and who hasn’t. The list in our example is small, but imagine if you had hundreds of attendees. It would take a long time to check this manually.
People who have made advanced payments have provided their membership ID and name, a note has also been left on how much they have paid upfront. At the party a note is made of all attendees and their membership ID.
The vlookup formula
Two formulas need to take place to complete the spread sheet. One of the formulas is a calculation and the other is the vlookup. A vlookup needs to be added in cell H3 to H8, which is the ‘Amount Paid’ section in ‘Members who attended’. Remember we mentioned earlier for a vlookup to work both ranges need to have an identifier, which is equal. We have the perfect identifier and that is the ‘Membership ID’.
You could also use the ‘Name’ field because they also look equal. However, it’s easy for names to be taken down incorrectly, and it’s always advisable to use an identifier, which you know should have no problems with incorrect matches.
Always name the range you’re going to vlookup
It’s always advisable to name the range, which you are doing the vlookup against. In this instance the range would be cells A3 to C7. In Excel, cell A3 to C7 would be seen like this A3:C7. You do not have to name the range if you don’t want to, but it is strongly advised to do so because complex spread sheets without named ranges on your vlookups will cause you headaches. Watch the video above for more information on naming ranges, but for our formula just know we called A3:C7 ‘PaidInAdvance’.
The vlookup sum
In cell H3 you would type the following formula:
The vlookup formula broken down
= You always start a formula with an equals sign
vlookup – is the name of the formula, and a formula is always open and closed between brackets ( )
F3 – This is the field in ‘Members who Attended’, which you are matching against ‘Advanced Payments’.
PaidInAdvance – This is the data range we named and covers A3:C7
3 – This is the column from the advanced payments you want to bring back data from. Meaning you wanted the vlookup to bring back the amount paid. If you entered ‘2’ instead of ‘3’ the formula would still work but it would have showed the persons name rather than amount paid.
false – Using false brings back an exact match. You can do an approximate match using true, but it is very unlikely you would ever use this.
NOTE: For the vlookup to work it needs the information your matching against in F3 to be the information in the first column of the information you do the vlookup against, which on this occasion is ‘Advanced Payments’.
The vlookup results
You can now see above that the vlookup has brought back the information on who has paid. The first row in ‘Members who attended’ in ‘Amount Paid’ shows #N/A. You need to get used to this sign because this is what a vlookup brings back if the vlookup could not match data. You can remove the #N/A if you wish by adding to the formula, but we will not cover that in this article. Generally speaking having the #N/A showing is a good idea because it instantly shows you where data did not match.
The calculation for the amount owed
The only thing, which is now missing is the calculation to find out who still owes money from the party. This calculation is not part of the vlookup, as we have already completed the vlookup. However, the calculation shows that a vlookup does not only bring back information, it can also be used as part of a calculation.
The sum you would enter in I3 is the following:
The above sum is working on the basis the tickets for the party were 15.00, it then subtracts from H3, which is the amount already paid.
For both the vlookup and calculation you would need to drag the formula down to the remaining cells.
Still unsure how a vlookup works?
If you are still unsure how a vlookup works or would like to gain more confidence. Take a look at the video at the top of this article because it uses a different example for using a vlookup.
A Complete Guide on how to use Microsoft Excel is recommended if you want to know more about Excel.
Please support my youtube channel
Please support my website by subscribing to my YouTube Channel. You can subscribe to my channel by clicking on the red icon below. Thanks