Hello. Here is a quick guide to the VLOOKUP function in Microsoft Excel. I am using Microsoft Excel 2013.
What is VLOOKUP?
The VLOOKUP function in Excel allows you to find information from a table/range by row given that you know a piece of the table.
For example, you may want to lookup the price of a certain car model in a table but all you know is the car model code.
A Grocery Store Example
Suppose I run a small grocery store and this is my inventory with prices. (A more realistic table would be much larger than this.) I want to use VLOOKUP to find the price of a food item given the name of the food item.
The table above was self-inputted. Select the table as follows, right click, left click on Define Name (near the bottom). A pop-window shows up. Change the name to a table name of your choice. I am using the table name of inventory.
If I want to find the price of an apple for example, I use =VLOOKUP(I6, inventory, 3 FALSE).
The cell I6 refers to Apple which is the item I want to “lookup”. You can click on the cell you want to lookup or type the cell (I6). The table inventory is being referenced. The number 3 refers to the 3rd column of the inventory table as I want to find the price of the apple. (If I wanted the counts for apple, I would use 2). I use FALSE in the fourth and last argument as I want an exact match. Selecting TRUE is for an approximate match but I do not want that.
A screenshot below summarizes the above. I find the price of an apple and the price of a mango.
So, that was VLOOKUP. What if we want the inventory value for each food item (Count * Price)?
We can use VLOOKUP again along with the PRODUCT function or the multiply symbol *.
As an example =PRODUCT(A, B) in Excel gives A multiplied by B. Here I want to multiply the count by the price for Avocado.
I use the product command where A is VLOOKUP(I13, inventory, 2, FALSE) and B is VLOOKUP(I13, inventory, 3, FALSE). (2 * 1.09 = 2.18)
The screenshot below summarizes the above.
Using Multiplication Symbol *
An alternative to using =PRODUCT is just simply using the multiplication symbol * to multiply.
For finding the value of all the mangoes in the inventory. I use two VLOOKUPS where one is the number of mangoes and the second is for the price of a mango.
I use =VLOOKUP(I14, inventory, 2, FALSE) * VLOOKUP(I14, inventory, 3, FALSE) which gives 6 * 1.19 = 7.14.
Vlookups look scary but they are not that bad. They are quite useful.
I forgot to format the cells for the values from VLOOKUP. One can format cells by highlighting the cells, right clicking, then left click on Format Cells. Select Currency in the Category window, make sure the currency is in $ and there are two decimal places.
Don’t forget the equal sign in the beginning of an excel function!
There is the =HLOOKUP function which is similar to VLOOKUP. HLOOKUP goes by rows instead of columns in a table. For example, in =VLOOKUP(I14, inventory, 2, FALSE), the 2 refers to the second column in the inventory table. If it was HLOOKUP the 2 would refer to the second row.
The featured image is from https://pbs.twimg.com/profile_images/511526105560276992/mO_sPhvY_400x400.png.