VLOOKUP
Finds the value specified (called lookup value) in the first column of the range specified and gets the corresponding figure from specific column in the same row where lookup value is found
Syntax of Excel VLOOKUP formula:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
In words:
=Starting from top of the range(look for this value in the first column, of this range, and get the figure in corresponding row of this column, by performing exact/approximate match)
Explanation
lookup_value: this is the value you want to lookup (find)
table_array: consider it a list of items that are arranged in numerous columns and rows. This basically makes up the whole area which you want to include in your search for specific value.
col_index_num: once the lookup value is found, this number tells excel from which column the value should be fetched corresponding to value looked up.
[range_lookup]: This is an optional argument. It can either be TRUE or FALSE. This is mentioned to dictate Excel if one wants an exact match or an approximate match. If it is left out empty then by default it is TRUE i.e. approx match for the lookup value.
In layman terms all of this can be said as:
- Look for specific value (mentioned as lookup_value) from top to bottom (V in VLOOKUP is for vertical).
- In the first column of the range (mentioned as table array which has several rows and several column). Whether the value to be found is by approximate [TRUE or 1] match or an exact match [FALSE or 0] depends on the option mentioned as [range_lookup]. Once found remember the row on which the value was found.
- Jump x number of columns to right to reach the specific column (x is mentioned as col_index_num) from the first column in the specified range (table_array) and fetch the value which is on the same row on which lookup_value was found.
Summary!
So basically VLOOKUP tells Excel to initiate search in vertical manner going from top to bottom looking for the value in the first column of the specified range. Once value is found jump or skip x number of columns to reach the intended column and fetch the corresponding value on the same row from the column reached. Whether the match to be made is exact or approx depends on the option i.e. if exact option is selected and no exact value found then formula will return an error.
Examples of Excel VLOOKUP Formula
Consider following data:
In order to find the number of units for A4 from the data above, we will use the following formula in cell B13:
=VLOOKUP(A13,A1:C11,2,FALSE) and it will return 88