VLOOKUP and its Application

VLOOKUP Function is one of the powerful Excel Formula that does a lot of magic for me. Interestingly, it enables me to carry out hundreds of calculations and analysis in minutes.

Though the golden rule is to search vertically (top to bottom) of the left column (usually leftmost) of the lookup table until it established a value that matches or nearly approximate to the one being looking up. Then return the corresponding value in the column that matches the one specifies in col_index_num argument of the VLOOKUP.

It can be use in two ways to search for either EXACT MATCH of the value you intend looking up in the table or closest (Approximately match) value you wish to return.

The syntax is:

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

Argument details:

  • lookup_value: the value you want to look up in the table
  • table_array: the cell range that containing both value to look up for and the one to return.
  • col_index_num: the number of column whose value you want to return
  • range_lookup: option logical TRUE or FALSE that specifies if you want excel to find either exact match or approximate match.

Better illustration

=VLOOKUP (search this value, in this given table, bring back the value in column number Y of the table as result, [but only if ONE of these conditions {Exact Match is established (FALSE) or approximately Match (TRUE)}])

In the table below we want to find the salary of each manager in column E using their Staff ID as the lookup value. Interestingly the salary information in column E in a table is to the right of Staff ID (supplied value) in column A, and this where we will allow the almighty VLOOKUP to come to our rescue.

To get this done and return the salary of the manager with “Staff ID” supplied in cell A2, the following VLOOKUP formula needs to be insert into cell B2.

= VLOOKUP (A2, A7:E13, 5, FALSE)

That is Cell B2 = VLOOKUP (search the ‘Staff ID’ appear in cell A2, in the table array (A7:E13), upon sited it (lookup value) bring back the corresponding value in column number 5, and it must be Exact Match (FALSE)

The result will appears as follows

Hope this post is helpful?

For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon


Shares 0