Related Functions:

The VLOOKUP function searches for a given value in the left-hand column of a data array, or table, and returns the corresponding value from another column of the array.

Note: Use VLOOKUP when you need to find things in a table or a range by row.

Syntax

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Arguments

Argument Description
lookup_value The value you want to look up

  The value you want to look up must be in the first column of the range of cells you specify in table-array
table_array The range of cells in which the VLOOKUP will search for the lookup_value and the value to return

  The first column in the cell range must contain the lookup_value
  The cell range needs to include the value to return
col_index_num The column number, starting with 1 for the left-most column of table-array, that contains the value to return
[range_lookup] Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

  TRUE
(default)
Assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value
  FALSE Searches for the exact value in the first column

Examples

  A B C D E
1 OrderID Quantity Unit Cost Total Cost  
2 10567 2 $3.50 $7.00  
3 10568 4 $5.00 $20.00  
4 10569 3 $7.23 $21.63  
5 10570 12 $2.99 $35.88  
6          
7 Formula Result Notes
8 =VLOOKUP(10568,A2:D5,2,FALSE) 4 Looks for the value 10568 in the first column (column A) of table_array A2:D7 and returns the value 4 found in the second column (Column B) of the table_array. The range_lookup FALSE returns an exact match.
9 =VLOOKUP(10568,A2:D5,4,FALSE) $20.00 Looks for the value 10568 in the first column (column A) of table_array A2:D7 and returns the value $20.00 found in the fourth column (Column D) of the table_array.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the supplied col_index_num argument is < 1 or is not recognized as a numeric value
  the supplied [range_lookup] argument is not recognised as TRUE or FALSE
#N/A Occurs if either:

  if [range_lookup] = TRUE
(or is omitted)
#N/A occurs if the smallest value in the left-hand column of the table_array is greater than the supplied lookup_value
      #N/A could also occur if the left column of the table_array is not in ascending order
  if [range_lookup] = FALSE #N/A error occurs if an exact match to the lookup_value is not found in the left-hand column of the table_array
#REF! Occurs if either:

  the supplied col_index_num argument is greater than the number of columns in the supplied table_array
  the formula has attempted to reference cells that do not exist. This can be caused by relative referencing errors when the VLOOKUP is copied to other cells