Excel’s VLOOKUP Function Explained

Excel's VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell. It's Excel's least-powerful lookup function.

732
Download the workbook of examples for the VLOOKUP function.
Download the example workbook.

Excel’s VLOOKUP function looks in the first column of an array and moves across the row to return the value of a cell.

VLOOKUP is the least-powerful and the most-restrictive lookup function that Excel offers. To learn about a few of its problems, see Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel.

Syntax for Excel’s VLOOKUP Function

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

lookup_value Required. The value to search for 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 in which VLOOKUP searches for the 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 if 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 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.

Example Formulas Using Excel’s VLOOKUP Function

You can download the example workbook here.

Excel's VLOOKUP function, example 1.

Excel's VLOOKUP function, example 2.

Some ExcelUser Articles about Excel’s LOOKUP Function

More Information about Excel’s VLOOKUP Function

  • VLOOKUP can return results from a closed external workbook.
  • One of the major limitations of the VLOOKUP function is that its lookup_value must be the first column of its table_array. No other Excel lookup function has this restriction.
  • Here’s the Microsoft help topic for the VLOOKUP function

You can download the example workbook here.