VLOOKUP function
Background

The very first thing we do in hotel is grab menu card, search for our favourate food item and then inspect its Price. Order is placed only after quick decision of price-satisfaction analysis. Our brain silently does VLOOKUP, which we never notice.

Excel offers same functionality in VLOOKUP function, and works in almost same pattern like our brain.

Syntax
=VLOOKUP(what,where,which,how)
1
2
3
4

VLOOKUP asks just 4 questions

1
 What to look ? Source value to be searched for. Must be some value / cell reference / expression
2
 Where to look ? Target range (in tabular format) where source value is to be searched for. Must be some range
3
 Which column ? Corresponding column number to fetch considering 1st column as 1, if the source value is found in target range
4
 How to match ? Method of matching
0 = Exact
1 = Approximate

VLOOKUP display #N/A as output, if it is unable to search the source value in first column of target range.

Example

Price list for various products of paint is given below. Based on the product entered in cell B8, its corresponding price should be fetched in cell B9

=VLOOKUP(what,where,which,how)
Product:

Try Online
Limitations
  1. VLOOKUP always searches the value in the first column of source range. Column number to fetch (i.e. which) must be on right hand side. Negative value (for left-hand side) is not compatible.
    excel vlookup function negative lookup impossible
    So, VLOOKUP will always search in first column. It does not have functionality to search in 2nd column and fetch result of 1st column
  2. VLOOKUP is not case-sensetive. Capital and Small everything is treated equally. Infact, matcing algorithm of Excel software ignores case.
  3. Number and Text are treated differently while matching. For example 395 written as number and text is different. So, cell format of source value and first column of the target range, must have same cell formating.
    excel vlookup number vs text behaviour
    In the above example, VLOOKUP will not output "Velvet" for lookup value of "395" as 395 in source value is of Number type and in Target range is of Text type. Excel will give output to #N/A i.e. not found. Small arrow in cell A3 notifies, that number in the cell is formatted with type Text and is preceeded by apostophe.
  4. VLOOKUP always treat result as number by default.
    excel vlookup blank cell output is 0
    In the above screen-shot, on pressing enter, VLOOKUP gives 0 as result even if cell B3 is blank.