The VLOOKUP function takes Excel to a whole new level, where it stops performing like a spreadsheet and in fact becomes more like a database. The VLOOKUP function allows you to find specific information that has been stored within a spreadsheet. So if you have a large list of parts or of sales items, not only will you be able to find that item or part, but you’ll also be able to find data that matches specific criteria, like the price of the part or how much of that item has been sold.
For example, let’s say that within your spreadsheet you want to set up a sales journal, where you want to see who sold what and how much revenue they made from these sales. You would generate a spreadsheet that had all of your employees names in one column, the date of the sale in another column, as well as what item was sold, how many were sold and the resultant revenue in the next three columns.
To make the sale journal easier, instead of having to manually enter what item was sold, figure out how much it was, and calculate the revenue by how many of those items were sold, Excel can do all of this for you with VLOOKUP. That means, all you have to enter is what the item was, and the quantity of items sold, and it’ll all be calculated for you. Nice and handy right?
So, how do you set it up? Well, your VLOOKUP is for the items sold, so this means that all Excel needs to know is where to find the information of what item costs what. You’ll need to set up another table here, simply with Item A to E, for example, with the value of each item in the cell next to it. It’s easier for Excel if you call this table something like, “_List” or “_Inventory”.
Returning back to your original sales journal, go to the cell beneath revenue, as this is where the function will need to be entered. Type =VLOOOKUP and select the cell under Item Sold, as the value will be determined by which item was sold. Now select the range from where this value of the item sold will be determined (that’s your separate sales list or sales inventory). You’ll need to type the name of this as you’ve titled it with Excel. You’ll also need to tell the VLOOKUP which column from your list or inventory that you want it to return. So, if the value of the item is in the second column, that’s the value you want it to return. Your cell string should look something like =VLOOKUP(C3,_List,2), where C3 is the item entered, _List is where VLOOKUP will get the information from and 2 is the second column of that list returned.
All that’s left to do is to finish the formula to multiply the item value by the quantity of the items sold. So returning back to your revenue column, select the cell with the formula and after the brackets type * which is the command for multiplication and then select the cell that contains the quantity, which will be next to the Item Sold cell. Therefore, your search string should look something like =VLOOKUP(C3,_List,2)*C4 and this will return the value under revenue that you want.
This means that within your sales journal, all you have to do is select which item was sold, and the quantity that was sold and Excel will work out the rest for you. It’s easy when you know how, sometimes you just need a bit of excel help.