Excel training videos

VLOOKUP and calculations in Excel 2007

Vlookup is a function which you combine with interesting calculations in large Excel worksheets. The V in Vlookup stands for 'vertical' . In essence, vlookup looks or searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The syntax you use to write the Vlookup formula is: '=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

The lookup_value is the value in the first column of the Excel data.

The table_array is two or more columns of data in the worksheet. It can be referenced by a range like 'A1:D7' or you can give it a name like 'myinventory'. 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.

The col_index_num is the column number in the table_array from which the matching value must be returned. The left-most column has the col_index_num 1, next column has 2, and so on.

The range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. This value is the source of most confusion in vlookup. If you set this value to 'TRUE' or omit it 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. Also under these conditions the values in the first column of table_array must be placed in ascending sort order or VLOOKUP may not give the correct value. If you set the range_lookup value to 'FALSE', vlookup will find an exact match and you need not sort the data in the first column of the table_array. In case there are two or more values in the first column of table_array that match the lookup_value, the first value found will be used. If you get an error like '#NA' or '#VALUE', vlookup has either not found an exact match or you have looked for a value in col_index_num less than 1. If the col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. You can correct such errors by using the TRIM or CLEAN functions.

If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.



VLOOKUP and calculations in Excel 2007