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.
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:
lookup_value: This should be either a literal value like
"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
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
range_lookup: This parameter can be omitted, otherwise it must be
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.
If you need to return multiple values (summed, counted, or otherwise aggregated) from the
table_arrayfor one individual row, you need to use another worksheet function, such as
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.
=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
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.
- HLOOKUP, the horizontal equivalent of VLOOKUP with very similar functionality except the role of rows and columns is reversed.