There are numerous functions in Microsoft Excel that have many uses but today the function we are going to discuss is called VLOOKUP. VLOOKUP stands for Vertical Lookup, which is used to perform a look up on a value vertically, and return an answer appropriately. It’s one of the many lookup and reference functions you can find in Microsoft Excel and its one of the important one too. VLOOKUP lets you search for a piece of information vertically in your spreadsheet, and then return a value corresponding to it. So it can be very useful if you want to extract a value from a table. For example, knowing the price of a specific item from a long list of items in a table will be a piece of cake with this function
The only limit here is the table being looked up using VLOOKUP must have a column with unique values so that the function VLOOKUP is not stuck in looking for duplicate values. If it’s not clear, read on and it’ll start to make sense.
In this example, we will make a list of items with their prices in a table. Then we will use VLOOKUP to know the price of a single item using just the item’s name. In this example all the prices are right there, but in a scenario with hundreds of items on multiple spreadsheets spanning across a few workbooks, this function can come extremely handy.
So to start with the example, you can create your own excel sheet or download our sample from here. Once downloaded, open it in Microsoft Excel. We will be using a simple example to make it easy for you to understand.
On the left, you will see some items with their names and categories in a table. The objective is to use VLOOKUP functions in such a way that we will just have to enter the item’ name on the table in the right, and its price and category should be automatically fetched from the table on the left.
Value “Scarf” is already there in H2 to begin with. We will use VLOOKUP in I2 to get the scarf’s price. Click on I2. then on the menu bar above, click on FORMULAS tab. Now choose Insert Function or press (SHIFT + F3) oInsert Function window will appear.
Type VLOOKUP under Search for a function and click Go. With VLOOKUP selected click OK.
VLOOKUP Function Arguements will now open. There are four arguments. The first three are bold, that means they are required, and the forth is optional.
First one is Lookup_value. It will be a single value (Item name in this case) which is a unique identifier, to lookup the price in B.
Table _array is the whole reference table in which the value (Price) will be searched. Click the little icon next to Table_array and click and drag to select the whole table WITHOUT the headers. Also Press F4 so that these cell addresses remain absolute and doesn’t changes when you click and drag this cell to apply this formula to other cells.
Once VLOOKUP finds the unique identifier in the reference table, Col_index_num argument will tell VLOOKUP the column number to search for the piece of information (price). As in the reference table, prices are listed in the second column with respect to the item names, so we will type 2 next to Col_index_num. Here, we are not entering 2 because the Price column is 2, we entered 2 because it is Column 2 in the reference table. (table_array). If the database that we need to lookup is in the second sheet, then we would select the table_array from the second sheet.
Range_lookup is used to find the closest match for the unique identifier in the reference table, but for it to be of use, your reference table must be sorted in ascending order, which it isn’t in this example. So type False next to it and Click OK. After pressing OK, in I2 the price of scarf will appear.
Similarly you can use the formula in J2 under Category to use VLOOKUP to find the items category. The only change you will have to make in the formula is to change the value for Col_index_num to 3 as the Categories of Items are in the third column in the reference table.
You can now click and drag the cell I2 and J2 below to apply the formula to the cells below too. But unless you type an Item’s name next to them, you will see N/A written in those cells. To remove it, we can use the Excel’s ISBLANK and IF functions together.
To do so, click on I3 showing N/A. then click on the formula bar to edit the formula. Change:
Now I3 will be blank until H3 is populated with an items name.
So this was all about VLOOKUP. The GIF Below is a demonstration on using the reference table from the second sheet.