How to Use VLOOKUP function (VLOOKUP formula) in Excel 

Friends, today we will understand in detail about a formula of MS Excel which is known as VLOOKUP.

 We use this formula called VLOOKUP where there are too many workers in a factory or too many children in a school. We have to get information about one of the many workers or about one of the many children. Meaning that we have collected as much information of a worker or any child inside our computer. VLOOKUP will tell us all that information very easily.

VLOOKUP style:

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

1.   lookup_value:- lookup_value means in relation to which value we have to check our data.

2.   table_array:-In table_array we will select the table array (one column to another column) as far as the data we want.

3.   col_index_num:- col_index_num means what column number data we have to take.

4.   range_lookup:-In range_lookup we have to select False or True. False means exact match and True means approximate match (default setting). You can also ignore range_lookup if you want. But instead of ignoring it, we use False for exact matching of our data.   

For this we take an example. Suppose we have a list of students. In this list, roll numbers of students, names of students, in which subject of a student are how many numbers and how many marks they have secured out of total marks etc. As you can see in Fig 1.

Fig 1

Now as if I want to know what is the name of the student of roll number 5 and in addition to this, whatever information we have collected about roll number 5 inside our computer, we get all the information immediately. So for this we will put VLOOKUP formula in this list.

How to apply VLOOKUP formula: 

As you can see in Fig 2(a), Fig 2(b), whatever data is in row 1 such as student name, gender, math, science, english, gk and marks etc. We have to copy and paste all the data in the same page in a separate box of any excel, as given in the image. 

Fig 2(a)

First of all we will put VLOOKUP formula on student name. We will write that = VLOOKUP (A16, A2: B10,2, FALSE). A16 in it means lookup value. Now we have to check our data with respect to this lookup value. Meaning that I have to put any roll number in this column of A16. A2: B10 means that we have to select the name of the student of the column from column A2 to B10. 

Fig 2(b)

This means that we can say that whichever student's roll number we enter, we can see the name of that student. “2” means the column of student name is another.  FALSE tells our data with exact match. Similarly, we will create a statement according to this formula in all our columns. Such as:

• Gender :                 =VLOOKUP(A16,A2:C10,3,FALSE)
• Math :                     =VLOOKUP(A16,A2:D10,4,FALSE)
• English :                =VLOOKUP(A16,A2:E10,5,FALSE)     
• History :                 =VLOOKUP(A16,A2:F10,6,FALSE)  
• Science :                =VLOOKUP(A16,A2:G10,7,FALSE)
• GK :                         =VLOOKUP(A16,A2:H10,8,FALSE)
• Marks Obtained :   =VLOOKUP(A16,A2:I10,9,FALSE)
• Total marks :          =VLOOKUP(A16,A2:J10,10,FALSE)

With the help of this formula, we have got all the information about roll number 5. With the help of this formula, we can get information about any roll number.

If you want to know about Count function then clickhere

SHARE

Ishan Kumar