VLOOKUP is a function to filter out data in a vertically arranged cell range. To understand what happens with VLOOKUP let us begin with a simple example of a stock inventory list as given below.
If you need to find the unit price or available stock information of the product having ITEM CODE a 02 what will you do?
You just scan through the first column to find the row belongs to the product a 02 then focus in to the required column to find out unit price $ 3.99 or available quantity 7 respectively.
This is the simple behavior of the VLOOKUP function.
Step 01: Setting up the data set
Open Microsoft Excel and create a new Blank workbook. Then enter your data as shown below. I would recommend you to download the free excel file which is all set for you.
Then choose a cell to enter your preferred ITEM CODE and another cell to show expected result.
We are going to enter required ITEM CODE in H3 and the PRICE of the item will show up in I3.
Step 02: Calling for VLOOKUP function
In Cell I3 type =VLOOKUP( which shows us some tooltips into the parameters required.
lookup_value : A value in the left most column of the data table, here it should be one of the values in “ITEM CODE” column or a reference to it. Since we input lookup value in H3, lookup_value parameter is set to “H3”.
table_array : the range of cells that contains the data to be filtered. Here it is “C4:E8”.
col_index_num : the number of the column to be filtered with respect to “lookup_value” counting from left most column. If you need to filter out price of a given item then this parameter should refer “PRICE” column, which is number three.
[range_lookup] : value (TRUE or FALSE) that specifies whether you want VLOOKUP to find an exact match or an approximate match. I will explain this later, until then, let us set the value to FALSE.
Finally, your function should look like this,
Step 3: LOOKUP for results
Now it is time to see if it works.
Enter one of the values in ITEM CODE column to cell “H3” and see what happens.
Great! That is what we wanted!