Advance Excel Learning - VLOOKUP Formulla - Software Applications - MS Excel - TIK Share

Advance Excel Learning - VLOOKUP Formulla


CA Pulkit Sharma

2013-05-05

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)                                                 

=VLOOKUP(O5(Object),P5:U17(Table containing Data),2(Column number),FALSE)

 

IF is  logical formulla and success of IF  formulla depends on how well you have framed your logic.

SUMIF is semi logical and semi  mathematical formulla, And now we will proceed to VLOOKUP function.

 

VLOOKUP is the most important formulla in DATA analysing, it directly does not affect the calculations but if used wisely can make your work very easy.

 

Vlookup is used to find out in a raw, what value a perticular column contains, against the given Object.

                                                                                                                                                                                                

Example                                                                                                                                                                                 

 

Student(A)

Science(B)

Maths(C)

Social(D)

Hindi(E)

Sanskrit(F)

Total(G)

10

Amar

60

43

50

56

60

269

11

Akbar

50

60

40

42

65

257

12

Anthony

30

25

50

55

58

218

13

Ram

80

89

90

98

95

452

14

Shyam

33

34

56

40

67

230

15

Geeta

35

78

85

80

90

368

16

Sheeta

32

34

21

34

45

166

 

We will track the marks of Amar from given Table.

(CellA19)Amar

60

43

50

56

60

=VLOOKUP($A$19,$A$9:$G$16,7,FALSE)

                                                                                                                                                                                                

So you can see that instead of just 7 students if we had 1000 students it would have been very tedious task to find the marks of each student.

So VLOOKUP formula is best when you have to extract the information of particular student out of many students.

                                                                                                                                                                                                

Now we will use the VLOOKUP formula with IF formula to see the each students result.

If student get more than 35 than PASS otherwise FAIL.

                 

(CellA27)Shyam

Fail

Fail

Pass

Pass

=if(vlookup($a$27,$a$8:$g$15,6,false)>35,"pass","fail")

 

you can see that by using IF and VLOOKUP formula, result of a student can be extracted whenever required.

 

                                                                                                                                                                                                

Exercise:                                                                                                                                                                                            

Try to find out in which subject Anthony has passed and whether he has passed considering total if Minimum in each subject 35 is required and Total should be more than 200?

 

Click here to Download the Vlookup file for exercise.

*Do give feedback if you feel style of learning lessons to be changed.