The Vlookup Guide

    
      Today we will discussed about the most useful topic of advance excel, so for us it is necessary to know that how to use this function and why this function is so much useful.

First we will understand that what is vlookup and why this is so important so first we will put a little 

look what is vlookup.

VLOOKUP, V stands for "vertical" which mean use VLOOKUP to lookup values in table that's 

arranged vertically. And the same for Hlookup stands for “Horizontal” but we will discuss that in later.

                         VLOOKUP (Lookup_value, table_array, col_index, [range_lookup])
                                                    (1)                     (2)             (3)                   (4)

(1)    Lookup_value          The value to look for in the first column of a table

(2)    table_array               The table from which to retrieve a value.

(3)    col_index_num       The column in the table from which to retrieve a value.

(4)    range_lookup           [optional] TRUE = approximate match (default). FALSE = exact match.

Here is the example:-

Here we will discuss about one by one but for the clarity we need one table to understand the picture clearly.

Here in this above image we can see that row no 3 is highlighted, suppose that  we have only cell value “George” in cell A3 and we have to look for the cost of “George”or we need to find the value of “Profit” of “George” so in this case we can use vlookup and after using vlookup we can get the value easily. How to find the value of “George”  in the column of “Cost” or “Profit”, see how it happens :- 

Simply type =Vlookup(1,2,3,4)

                    =Vlookup (lookup-value{ we are looking for},table-array{total table area from A1 to D9},col-index{in which column we the value is exist},[range-lookup]{iether we want exact value or near about}               

Now we need to put the value in the above format

       =Vlookup(A13,A1:D9,3,False)

      A13      =we are looking for “George” value in cost column so “George” is located in cell A13.

      A1:D9  =total table area, we can easily seen that total  data is from cell A1:D9

     

      3          = 3 is the column no. where my value is located

True/False =    and last argument is True/False which means either we want the value exactly or near about will do.      

After putting these value into the formula and press enter, you will get the result that “George” gave “7412” value in column 3 and if in the formula we will change column no. “4” instead of “3” you will get the result  from 4^th  column i.e. = 12025 the value of “George” is located in column no. “4“

Limitations of Vlookup

Vlookup has some limitation and in the following example we will discuss about it.

1.        Vlookup will always look for the data from extreme left to right, not from mid to right , in the below given image  vlookup will find the data from Column A to Column D not form Column B to Column C  or Column D. 

We can understand it from below given example :-

 We have cut a piece form above image to understand this better way and in this below given screenshot we can  see that column (H) and column (I) are blank but we can find the value for both the places.

By the vlookup formula if we want to look for the value for H2 and I2 the formula would be

=Vlookup(F2,Table array would be (A1 to D9), column no would be 3 or 4, and last step  is True/False)

True stand for = Match nearest value

False stand for = Match exact value

I want to say from this example we cannot find the result by putting the below given example

=Vlookup(G2,Table array would be (A1 to D9), column no would be 3 or 4, and last step  is True/False)

=Vlookup(G2, A1 : D9, 3 or 4, False)

If we are applying this formula so we have to take notice that when we will look for G2 so the table array would be B2:D9 not by A1:D9 if we follow this the result will be come out because in this case column B2 would be extreme left.

2.   Vlookup will not look for the data from right to left, example below :-

You can see in the below given image the limitation of vlookup that it cannot look the data from right to left.

3.      If we have applied vlookup formula somewhere and we will insert another column in the table array field so everywhere we have applied vlookup formula (only those formulas which has the base from that table array field) all the formula will not produce the correct result because vlookup will count the column no. and then give the result but if we insert another column in the table array field the result would not  be the same.      

In the below given image we can see that when we insert a column before the Sales column the result is not the same and now it is a blank cell. Here above in formula bar you can see that the formula is same as it is appearing in the above image but in above image the value appeared after vlookup formula applied but in the below given image the value is not appearing but the formula is same to same, only difference is we have inserted a column before sales column.

So all this is happening because vlookup has a limit that if we insert a column between the table array it will not produce the same result.

So all these are the limitation of vlookup we have solution for the same also, like if column inserted so we have the solution, even how vlookup goes to left. 

All we will discuss in the  future tutorial.

Till then

Good bye

Narendra