In this article, we demystify VLOOKUP by way of a real-life example.
Well create a usable Invoice Template for a fictitious company.
VLOOKUP is an Excel function.
What sort of things?
Well, any sort of thing.
It doesnt really matter.
Heres an example of a list, or database.
In this case, the unique identifier is in the Item Code column.
Our sample database above satisfies this criterion.
The hardest part of using VLOOKUP is understanding exactly what its for.
Which of these pieces of information will it pass you back?
Well, you get to decide this when youre creating the formula.
Typically you would use this sort of functionality in a reusable spreadsheet, such as a template.
Interestingly, this is the step that most people get wrong.
Where do we want this description put when we get it?
In cell B11, of course.
So thats where we write the VLOOKUP formula: in cell B11.
Select cell B11 now.
The system would return us a list of all lookup-related functions in Excel.VLOOKUPis the second one in the list.
Select it an clickOK.
The first argument we need to complete is theLookup_valueargument.
We must grab the item code we entered earlier (in A11).
Now we need to enter a value for theTable_arrayargument.
In other words, we need to tell VLOOKUP where to find the database/list.
Now we need to enter the third argument,Col_index_num.
In this particular example, we wish to have the items description returned to us.
Finally, we need to decide whether to enter a value into the final VLOOKUP argument,Range_lookup.
This argument requires either atrueorfalsevalue, or it should be left blank.
Weve entered all the information required for VLOOKUP to return the value we need.
Note that the new formula must be created in cell E11.
If we decided to buy 2 of these items, we would enter a 2 into cell D11.
In fact, weve learned all were going to learn in this article.
Its important to note that VLOOKUP can be used in other circumstances besides databases.
This is less common, and may be covered in future How-To Geek articles.
Our invoice template is not yet complete.
