How i can use vlookup function in MS.excel...plz help?

Question

"vlookup" is a function in MS excel... me wana use that function any body can help me about that function

Answer 1

1st use VLOOKUP function, if the data need to be looked up are in columns(header/title on each column top), use HLOOKUP instead if data are in rows (header/title on each row left)

General syntax: VLOOKUP(<LOOKUP VALUE>, <LOOKUP RANGE>, <COLUMN FROM LEFT IN LOOKUP RANGE>, <EXACT MATCH?>)

If the function find the match, it will return the corresponding value, if not, returns #N/A

Here's how to use vlookup by a couple of examples,

Example 1 you want to get the grade by putting the point in a formula

1. set up a table of grade and its minimum point, say to attain A grade, one should have 90pt min.

then a table like

A1 = 0 B1 = F

A2 = 60 B2 = E

A3 = 70 B3 = D

A4 = 75 B4 = C

A5 = 85 B5 = B

A1 = 90 B6 = A  

Note: 1st column of table array should be in ascending order or no result

2. put points to grade say for 2 records in

D1 = 91, D2 = 74 , and E1 = =VLOOKUP(D1,$A$1:$B$6,2,TRUE) then E1 will return A

copy the formula to E2 then it will return D

Note: $ signs is need to fix table location as formula is copying down column E  

      parameter 2 in formula is the return value(grade)

      parameter True in formula is for closest(equal or less) match.

Example 2 using the same table above, get the corresponding alphabet of the same row when you are given a number, the numbers will only be 0, 60, 70, 75, 85, 90

The formula will be =VLOOKUP(D1,$A$1:$B$6,2,False)

      parameter False in formula is for exact match.

so if D1 is 75, then the formula will return C

Hope this helps.