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