For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

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, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Download the workbooks.)

Note that VLOOKUP isn't the most-powerful lookup function that Excel offers. To understand why, see Why INDEX-MATCH 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.

Example 1 of Excel's VLOOKUP worksheet function.

Example 2 of Excel's VLOOKUP worksheet function.

Other ExcelUser Information

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards