Learn how the VLOOKUP function works

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.

Item CodePriceQuantity
A_01$4.005
a 02$3.997
B$3.000
c$101
5$1.003

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.

[emaillocker][wpdm_package id=’252′][/emaillocker]

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!

This Post Has 3 Comments

  1. Thanks man..nicely explained!

  2. Really simply explained about the vlookup function. Thank you

  3. I really love your website.. Excellent colors & theme.
    Did you develop this amazing site yourself? Please reply
    back as I’m trying to create my own site and want to learn where you got this from
    or just what the theme is called. Kudos! I have been browsing online more than three hours these
    days, yet I never discovered any attention-grabbing article like yours.
    It’s pretty price enough for me. In my opinion, if
    all web owners and bloggers made just right content material as you did, the
    net might be a lot more useful than ever before. Its such as you learn my thoughts!
    You seem to understand a lot approximately this, such as you wrote the e-book in it or something.
    I think that you simply can do with a few % to
    pressure the message home a little bit, but other than that,
    this is great blog. An excellent read. I’ll definitely be back.

Leave a Reply

Close Menu