Vlook up Formula in Excel with Step by step illustrations

VLOOK UP

This function is very useful for that kind of job in which we have to find the things from one table and put in front of values present in other table.

Comparison of two Formulas

1. Index match 
2. Vlookup 

                   (As discussed in discussion relating  Index match )

               Index match and Vlookup both are similar in their work .
                 Using both in practical examples it is observed
                           that  Index match is better than Vlookup formula.

                                     

                                              Because;

1. In the Index match formula lookup value can be from any column whether they are in right side or the left side of column containing our required value.
2. It requires only two columns :

                   a). Column in which Lookup value is present.

                   b). Column from which Resultant value is taken.

 However

1. Vlookup formula works only from left side to right side of table.
2. It requires a continuouse array of table and if table contains many column or some column are hidden then it becomes very difficult to calculate index_no for our formula. 

Second method (Vlookup formula) 

Let we have a table having three columns first column contains the company names second column contains abbreviation of  company , and third name contains the total number of employees working in that company.

 

Now we want the excel put automatically the number of  employees if we  write the abbreviation of the company in a blank cell. Or;

We want the excel put automatically the Name of company, if we  write abbreviation of company in a blank cell.

As shown below writing the Abbreviation  "acl" in cell F7, Vlookup formula gave result in cell I7

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1.    lookup_value : It is the value about which we want the information.(Values in “F” column e.g acl, BATAA)

2.  table_array: It is the table from which we want to extract information. (A5:A12 is table array for our exapmple.)

3.  col_index_num: It represents the Number of column from which we want to extract information (In our example 3^rd column is our required information)

4.    [range_lookup]:  What kind of match we want .i.e for exact match select “False” we have selected “False” )

Write formula in the pattern given below.

                       
                       Its done......

For any query and or formula you can write in comments section.........