Excel Tips: How to apply the VLookup Function
Lots of our students and Facebook fans have told us they wanted to learn Excel’s VLookup function and how to apply it into a simple spreadsheet. The VLookup function is by far an extremely powerful tool if applied correctly and you’d be amazed at just how simple this function is to learn.
Before we get started, you should have at least a basic understanding of the functions of excel. If not, then see my post about “the ultimate guide to excel shortcuts and hot keys”. The VLookup function works in all versions of Excel, and can even work in Apple and Google’s version of Spreadsheets.
What is a VLookup?
In short, VLookups allow us to look up specific information relating to products or prices, or anything else you wish to add. Let’s use the VLookup function to find the price of the photo frame. You can already see in this simple example that the price is $9.99, but we are working on a simple example. The approach that I am covering today will allow you to apply this to more complex spreadsheets and this is where the VLookup function becomes an exceptionally powerful tool to use in Microsoft Excel.
I’m going to add our formula into cell E2. I’ve chosen this particular cell randomly, but you can use any cell of your choice. It really doesn’t matter what cell you decide to use. Like any other excel formula, you must start with the equals sign to begin telling excel that your applying a function.
So, in cell E2, enter =VLOOKUP(
Add The Arguments
Arguments tell us what to search for and where. Our first argument will be the name of the product. Because in this example we are using Photo Frame which is in text, we need to begin using the double quotes.
So your function will now start look like this
=VLOOKUP(“Photo frame”, A2:B16
Our second argument will the cell range that contains the data, so in our example it is in A2:B16. As you can see from the image above, this covers everything that we are looking up.
The next argument that we must use is the column index number. In this case, we are trying to find the price of the item, and the prices are contained in the second column. This means our third argument will be 2:
=VLOOKUP(“Photo frame”, A2:B16, 2
The fourth argument tells VLOOKUP whether to look for approximate matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for approximate matches. Generally, this is only useful if the first column has numerical values that have been sorted. Because we’re only looking for exact matches, the fourth argument should be FALSE. This is our last argument, so go ahead and close the parentheses:
=VLOOKUP(“Photo frame”, A2:B16, 2, FALSE)
That’s it! When you press Enter, it should give you the answer, which is 9.99.
Here’s how to works
Now we should take a look at how this formula works. It searched vertically down (hence the term VLookup), this is where it found the text “Photo frame”. It then moved to the second column where it found the price.
If you would like to find the price of a second product, no problem. Simply change your first argument.
=VLOOKUP(“T-shirt”, A2:B16, 2, FALSE)
=VLOOKUP(“Gift basket”, A2:B16, 2, FALSE)
Let’s try another example
Are you ready for a slightly more complicated example? Let’s say we have a third column that has the category for each item. This time, instead of finding the price we’ll find the category.
To find the category, we’ll need to change the second and third arguments in our formula. First, we’ll change the range to A2:C16 so it includes the third column. Next, we’ll change the column index number to 3 because our categories are in the third column:
=VLOOKUP(“Gift basket”, A2:C16, 3, FALSE)
When you press Enter, you’ll see that the Gift basket is in the Gifts category.