Excel and Macro: VLOOKUP (simple)

The formula for VLOOKUP is to search for a value in a cell in the first column and return the value of another cell that is in the same row but different column.

Simple example is like in table below:



We want to know what is Andy's favorite fruit. From the table, immediately we can find that the answer is Orange. The cell that hold's the answer is in the same row but in different column. The function of VLOOKUP is to find information in such a manner.

Formula of VLOOKUP:

=VLOOKUP(lookup_value, table array, col_index_num, [range_lookup])

In simplified form:

=VLOOKUP( A , B , C , D )

A - the value you want to search for. In the example above, we want to find Andy.

B - the table you are looking in to find Andi. In the example above, we select the whole table.

C - the column number (from the left) of where the value you want returned is located. In the example above, we are looking for Favorite Fruit, hence the 2nd column from the left.

D - this is to provide exact or approximate value...in most instance people just put the number zero for this one.

Using the above example, the input in the formula would look like the following in Excel:

=VLOOKUP( "Andi", A1:C6 , 2 , 0 )

This will return you the value "Orange".

Note that A can be cell number or text.

In the above example, if we use cell number, it will be A2. This is because Andy's name is in cell A2.



If we use text, we must type the text and enclose it in double quotes "Andi".



This is perhaps an the simplest way to explain how VLOOKUP works. Hope it is helpful for beginners. In later posts, we will show how to use VLOOKUP in more advanced manner.