How to use VLOOKUP Function in MS Excel

VLOOKUP can be used to search for data in a table and return value from any specified cell in same row. The data which is searched for should be in first row of data range. Also data list should be in vertical direction.

VLOOKUP searches for data in top to bottom direction from first column of data.

Syntax VLOOKUP function

=VLOOKUP (value, table, col_index, [range_lookup])

Where

value is  the value that you want to look up in the table

table is the cell range or name of the lookup table that contains both the value to look up and the value it is required to return.

column_index argument in the VLOOKUP function is the number of the column whose values are compared to value argument in a vertical table.

range_lookup is optional argument and is the logical TRUE or FALSE that specifies whether look for n exact or approximate match for the value in table. If not provided default value for this variable is FALSE.

Errors in VLOOKUP function in excel

Similiar to HLOOKUP function VLOOKUP also generates error if

  • # VALUE! error – you will get #VALUE! error if row_index argument is less than 1.
  • #REF! error – you will get #REF! error if column_index argument value is more than number of columns in table.

Example of use VLOOKUP in excel

Here we have a table with details of sale for items with Sale ID, item name and quantity.use VLOOKUP function in MS Excel

Now if we want search name of item from ID and quantity sold we will use VLOOKUP

Here VLOOKUP searches for sale item mentioned in cell G2, in table array A2:C8 and return item from column number 2 for item sold and column number 3 for quantity sold.

Formula used to search item from Sale ID in table

=VLOOKUP(G2,A2:C8,2)

Now to lets find out how is this formula working

value argument is referring to cell G2 which contains Sale ID

table argument used is A2:C8 which is range of your table

column_index argument is 2 referring to row number 2 of table which has list of items in this rows.

range_lookup argument is not provided i.e. excel will treat it as false and search for exact match.  So if exact Sale ID is not found then formula result will be #N/A.

Next formula which has been used is similar to one above

=VLOOKUP(G2,A2:C8,3)

lets see how this works, here we are trying to search for quantity sold from Sale ID.

value argument is referring to cell G2 which contains Sale ID

table argument used is A2:C8 which is range of your table

column_index argument is 3 referring to row number 2 of table which has list of items in this rows.

range_lookup argument is not provided i.e. excel will treat it as false and search for exact match.  So if exact Sale ID is not found then formula result will be #N/A.