VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP function is one of the lookup and reference functions, when you need to find things in a table or a range by row. Before explaining it in words let us take an example where it will be useful.
Understand VLOOKUP Excel function with example
Above image shows Ms Excel containing data base of student (Column B to E), with Roll Number, Name, Age and Percentage.
What if I require data of student with a particular roll number.
Now suppose if I enter roll number of student in cell ‘G4’ And want to get some field say Percentage of student whose roll number is given in cell ‘G4’.
Manual Searching – Inefficient way
Crude way is to scan for roll number in the entire data base of student manually and then see percentage of associated roll number. Doing this manually will take lot of time and unnecessary efforts. But don’t worry, all this task can be done automatically by using VLOOKUP function.
VLOOKUP is exactly a function that is needed for this task. To get percentage of student given an roll number you just need to type following formula in excel. In simple words VLOOKUP means something like this
VLOOKUP(Known value that needs to be searched, range of cells where this value needs to be lookup, the column number that must be returned by VLOOKUP function, Exact Match (FALSE) or Approximate Match (TRUE) )
Formula to find the Percentage of student (4th column in database) with roll number given in G4 (Exact macth), from all the students data in B4:E13 is as follows.
The above function searches value of Cell G4, first column of the range of elements (B4-E13). After finding the row containing roll number given in Cell G4 (here B5-E5), Excel returns the value of the 4th Cell of B5-E5 i.e. excel returns value of Cell E5.
=VLOOKUP (LOOKUP-VALUE, TABLE, COLUMN-INDEX, [LOOKUP-RANGE])
- LOOKUP-VALUE : This is the known value that needs to be searched in first column of table
- TABLE: Table from which needs value needs to be retrieved.
LOOKUP- VALUE that needs to be searched should column of this TABLE
- COLUMN-INDEX : Column of TABLE whose value needs to be retrieved.
- LOOKUP-RANGE : This can either be TRUE or FALSE. To find EXACT match of LOOKUP- VALUE in TABLE and use FALSE for to search approximate match of LOOKUP-VALUE in TABLE use TRUE
VLOOKUP function of Excel is designed to retrieve data in a table organized into vertical rows, where each row represents a new record. The “V” in VLOOKUP formula stands for vertical:
VLOOKUP function only looks right
After locking the searched row, value only on the right side could be retrieved.
VLOOKUP function returns First Match
VLOOKUP returns result of first match. VALUE being look up appears more than once, then only the first match will be used for retrieving data.
VLOOKUP function searches LOOKUP value only in first column of TABLE
LOOKUP-VALUE is searched only in the first column of TABLE.
VLOOKUP Function: Exact Match vs Approximate Match
Exact Match: In most of the cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, marks of student from roll number.
Approximate Match: This is little bit trick. It words when value being searched is arranged in ascending order. A classic example is finding the right discount rate based on a purchase amount. In the example below, discount rates are given in Table ‘B3:C2’.
Even with approximate match it first tries to find exact match. e.g. VLOOKUP with 5000 give 15%.
If exact match is not available then , VLOOKUP drops back with next higher search. Suppose value being searched is 1990. Table does’t has 1990. In this situation, VLOOKUP tries to find the next highest number (In this example, next highest number is 2000). Then VLOOKUP backdrops (takes value above that) by one row (here it selects 100 which is row above next higher value). Hence on searching 1990 in VLOOKUP with approximate match it returns value corresponding to 100 which is one row above its (1990) next highest value (2000).