explain vlookup formula in excel

• Changed type Tony Chen CHNMicrosoft contingent staff, Moderator Thursday, September 12, 2013 1:51 PM

Hi,
Vlookup formula is an useful tools in Execl.
It will help us search for a value in the first column of a table array and returns a value in the same row from another column in the table array.
The syntax of these functions are defined as follows.
VLOOKUP Function
   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    
where:
          Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   col_index    The column number in the table_array for which the
                matching value should be returned.

   range_lookup It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP finds an exact match. If an
                exact match is not found, the #N/A error value is returned.

 

Here are some examples:
a) =VLOOKUP(1,A2:C10,2) 
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row. (2.17)

b)= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE))  
Calculates the retail price of diapers by adding the markup percentage to the cost. ($28.96)

c)=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) 
For the fiscal year 2004, finds the age of the employee with ID equal to 5. Uses the YEARFRAC function to subtract the birth date from the fiscal year end date and displays the result as an integer using the INT function. (49)

For more detail information, please refer to the following link:

http://office.microsoft.com/zh-cn/excel-help/vlookup-HP005209335.aspx

http://support.microsoft.com/kb/181213/en-us

Regards,

George Zhao
TechNet Community Support

• Marked as answer by Tony Chen CHNMicrosoft contingent staff, Moderator Thursday, September 12, 2013 1:51 PM