Microsoft Excel Training Videos

Vlookup in Excel:
Find information in a large Excel worksheet

VLOOKUP in Excel 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 V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value: The value to search in the first column of the table array (Array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. Table_array Two or more columns of data. Use a reference to a range 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 The column number in table_array from which the matching value must be returned. A col_index_num 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. If col_index_num is:
  • Less than 1, VLOOKUP returns the #VALUE! error value.
  • Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
    • Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
  • If TRUE or 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 returnedd
  • The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
  • If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. 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.
  • 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.
  • When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value.
  • 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.
How to implement Vlookup in Excel:
  • First sort your Excel data in ascending using the 'sort' feature in Excel
  • Then start writing the Excel function '=vlookup(....)'
  • In the vlookup_value specify the item you are looking for, eg. 'shirt'
  • Next select the table_array, eg.A2:B7
  • Now define the column_index from where you wish to find the corresponding data, eg. 2
Watch the training video below to see how Vlookup is implemented quickly and easily.

Further reading on Vlookup function in Excel

Using the Vlookup function in Excel


Vlookup in Excel