Super User

About

Ask Question
Tag Info
Info Newest Frequent Score Active Unanswered

VLookup is a Microsoft Excel function that searches for a value in the left-most column of a table array (a range), and returns a value in the same row based on the column number from which the user wishes to retrieve the value.

Usage

Click on an empty cell in Microsoft Excel and type =VLOOKUP. Click the fx icon to view interactive help that will assist you in filling out the parameters, or fill out the parameters as follows:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value: This should be either a literal value like 12 or "hello", or any function that returns a reference to a single cell. Do not try to use a Range here; if you think you need a range here, you probably need to use a different worksheet function.

table_array: This should be a rectangular array of cells containing at least two columns (a "key" column and a "value" column). The leftmost column in the table array is where Excel will look for the value specified by the lookup_value parameter (see above). The "value" column will be read from in the first row where the lookup_value is found in the "key" column. This read value will then be returned as the result of the VLOOKUP() function.

col_index_number: This should be a number greater than 0. If the value is 1, the VLOOKUP() function will return values from the leftmost column in the table_array range. If the value is 2, it will return values from the next column to the right, and so on. Do not specify negative values, values less than 1, or values greater than the total number of columns in table_array.

range_lookup: This parameter can be omitted, otherwise it must be TRUE or FALSE. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match.

Pitfalls

  • If you need to return multiple values (summed, counted, or otherwise aggregated) from the table_array for one individual row, you need to use another worksheet function, such as SUMIF() or SUMIFS(). You can also use a pivot table for this functionality.

  • If you need multiple lookup_values for a single row, you need to use another worksheet function or set of conditionals.

Alternatives

The most versatile alternative is a combination of INDEX and MATCH.

=INDEX ( array , row number )
=MATCH (  lookup value , lookup array , match type )
=INDEX( array, MATCH( lookup value, lookup array, match type)

Simply put - INDEX(column to return, MATCH( lookup value, lookup array, match type)). This will lookup the matching row for the index column.

You can reverse it like this =INDEX(MATCH(lookup value, lookup array, matchtype), row to return) which looks up the matching column for the index row

Version Support

VLOOKUP() is known to be supported from at least Microsoft Excel 95 (version 7.0), perhaps earlier, up through and including the latest version as of this writing, Excel 2013. It is also supported on Mac OS X versions of Office.

See Also

  1. HLOOKUP, the horizontal equivalent of VLOOKUP with very similar functionality except the role of rows and columns is reversed.

References

  1. Microsoft Office Help article on VLOOKUP
History Excerpt history