Vlookup function in Ms excel allows us to search in first column of a given data range and return a value from any column in corresponding row.
If exact match is not found, vlookup function can return value by approximate match.
Syntax:
VLOOKUP (lookup_value,table_array,col_index_num,range_lookup)
Lookup_value: this is the value to be found
Table_arry: this is the range which contains data
Col_index: this is the number of the column which will be returned on successful match. For example, if col_index is 2 then value in second column from left of given range is returned.
Range_lookup: range_lookup can be either TRUE or FALSE. If it is FALSE, then value will be returned on exact match. If range_lookup is TRUE or omitted and exact match is not found then next largest value which is less than lookup_value is returned.
#N/A error is returned if no match is found.
Vlookup Example:
Following picture contains data of products. Out task is to find out price of product no. 2034. To achieve this, use following formula
=VLOOKUP(2034,B6:E10,4,FALSE)
In above function, 2034 is the value to be search, B6:E10 is our data range and 4th column is returned when exact match is found as shown in figure below
If you want to find the unit of product No. 2034, use following formula
=VLOOKUP(2034,B6:E10,3,FALSE)
What will happen if we use same formula for product No. 2070 as shown below.
=VLOOKUP(2070,B6:E10,4,FALSE)
As Product 2070 is not in out data, #N/A error will returned
But if we use TRUE in last argument of function, excel will match it with largest value which is less than 2070. Hence product no 2069 is match and it price which 6 will be returned as show in figure below.
i use it for learning