Lookup and reference
VLOOKUP Function
Looks in the first column of an array and moves across the row to return the value of a cell
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download
the workbooks.)
Note that VLOOKUP isn't the mostpowerful lookup function
that Excel offers. To understand why, see
Why INDEXMATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num,
range_lookup)

lookup_value Required.
The value to search in the first column of the table or
range. This argument can be a value or a reference. If the
value is smaller than the smallest value in the first
column of the table_array argument, VLOOKUP returns the #N/A
error value.

table_array Required. The range
of cells that contains the data. You can use a reference to
a range (for example, B2:E8), or a range name. The values in
the first column of table_array are the values searched by
lookup_value. These values can be text, numbers, or logical
values. Uppercase and lowercase text are equivalent.

col_index_num Required. The
column number in the table_array argument from which the
matching value must be returned. A col_index_num argument of
1 returns the value in the first column in table_array; a
col_index_num of 2 returns the value in the second column in
table_array, and so on. Excel returns an error value of the
table_array argument is less than 1 or greater than
the number of columns.

range_lookup Optional. A logical value that
specifies whether you want VLOOKUP to find an exact match or
an approximate match...

If range_lookup is either TRUE or is omitted, an exact or
approximate match is returned. If an exact match is not
found, the next largest value that is less than lookup_value
is returned.

Important If range_lookup is either TRUE or is omitted, the
values in the first column of table_array must be placed in
ascending sort order; otherwise, VLOOKUP might not return
the correct value.

If range_lookup is FALSE, the values in the first column of
table_array do not need to be sorted.
If the range_lookup argument is FALSE, VLOOKUP will find
only an exact match. If there are two or more values in the
first column of table_array that match the lookup_value, the
first value found is used. If an exact match is not found,
the error value #N/A is returned.
Applies To
Excel 2003 and above
Remarks
 VLOOKUP can return results from a closed external
workbook.
Examples
Currently, more than
four dozen worksheets are included. You can
download a zipped file of all example
workbooks here.
Other ExcelUser
Information
Other Help
