Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

VLOOKUP

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

Rational

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

Description

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.

Syntax

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

Parameters

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

HLOOKUP

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 »
Lookup a row or column of a table in Excel

LOOKUP

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 »
Returns a specific position from a list in Excel

CHOOSE

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

Read More »