=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?

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

Posted 4 years, 5 months ago by CA Pulkit Sharma

No response yet, be first to reply.