Lookup a value in a left column of a table or an array in Excel
Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table


Lookup a value in a left column of a table or an array.


The Excel VLOOKUP function extract data from a given column of a table or an array of values. So, if a given data is laid in a vertical pattern which means heading in 1st column and value begins from the 2nd column, and we need to look up and retrieve data from left to right for the value, in such cases we use VLOOKUP. The VLOOKUP function is a ‘vertical (V)’ lookup.


=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])


lookup_value – The value to lookup in the first column of the table
table_array – The table to extract lookup_value
col_index_num – The row number to return the corresponding value
range_lookup [optional]A Boolean argument. TRUE (Default) – to lookup a partial match and FALSE for an exact match. The function returns #N/A error value if VLOOKUP cannot find any value as per arguments (Exact or Partial).

Return Value

The corresponding value of a given column number

Key Notes

  • The VLOOKUP function returns the value by going right to the specified column (vertical) and returns the value in the formula cell.
  • The VLOOKUP function is not case sensitive.
  • The VLOOKUP function works just like HLOOKUP function except that value lookup is across a specified number of rows (horizontal).
  • In case [range_lookup] argument is TRUE (partial match) the function matches the nearest value, which may not be the expected result. It is advisable to select FALSE if you require an exact match of given criteria.
  • The first column of the lookup table (table_array) needs to be sorted in ascending order if [range_lookup] is TRUE. Otherwise, VLOOKUP may return an incorrect value.
  • If the lookup_value argument is text (and the [range_lookup] is FALSE), the user can use the wildcard characters asterisk (*) and question mark (?).
  • A question mark matches a single character and an asterisk matches multiple characters. A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.

Example (Image above)

When lookup value in column and return value of another row like here as table is vertical form and  user want cost of sales in quarter 1st  for this we use formula

=VLOOKUP(C11,$B$4:$D$8,3,FALSE) it return 170.


Keep Reading Similar Functions:

Lookup a value in the top row of a table or an array in Excel


The HLOOKUP function extract data in a horizontal pattern which means heading in 1st row and value begins from 2nd row, and retrieve data from left to right.

Read More »
Returns a specific position from a list in Excel


The CHOOSE function returns specific information from a list of data points, =CHOOSE(4,”Railways”,”Roads”,”Airways”,”Waterways”) returns Waterways.

Read More »
Lookup a row or column of a table in Excel


The LOOKUP function search in single row or column for a given value and return value from the same position or, next row or column.

Read More »