The purpose of the vlookup function is to look for a value in a list and returns the results found within the same row of the value (in the 2nd Column, 3rd column , etc) of the list. Vlookup worksheet function will deliver different results, depending on what has been provided in the formula. Therefore it is important that we understand how the vlookup works.

First, let�s start with a simple example #1.

Assuming you have a list given below:

In cell A10, enter the vlookup function given here (without inverted commas): "=VLOOKUP(70901005,$A$1:$B$7,2)"

You will notice that the cell will return the result "25 days". It is the value given in Cell B5. Let's find out what was the instruction given in the vlookup formula that leads to this result.

In the formula, we have indicated in the vlookup formula that we
are looking for the value "**70901005**" vertically (from range
**A1 to A7**). When the value is found, it was instructed to return
the value in the 2nd column denoted by the number 2 in the formula
"=VLOOKUP(70901005,$A$1:$B$7,**2**)".

Note that we have entered the range $A$1:$B$7 into the formula. The range B1 to B7 is included because we need to tell Excel to look for the results within a specific range. If we are to include a larger range into the formula, it will take up more memory as Excel would have to look through more rows for the values.

Now let's replace the value "**70901005**" with "**70901002**" which
is not found in the list. In this case, Excel will return the results associated
with a value in Column A that is smaller that the lookup value ("**70901002**").
Since "**70901001**" is smaller than "**70901002**", Excel will accept
this value and gives you the results "**COD**". Do note that the Invoice
No must be arranged in ascending orders (smallest to the largest number)
for the vlookup function to work.

If we are to enter the word "**false**" at the end of the formula
like this =VLOOKUP(70901005,$A$1:$B$7,2**,FALSE**), you will notice
that the results turns into "**#N/A**". The word "**false**" has
triggered another instruction to Excel. We have indicated that we do not
want the vlookup function to return any result if the value ("**70901002**")
cannot be found. When we omitted this part of the formula, Excel by default
assumes that we want to find the closest match to the value. Hence,
"**COD**" was returned when we did not enter the word "**false**"
into the formula.

Vlookup function is very useful when it comes to merging 2 sets of r ecords. Assuming that you have two set of records as shown in the diagram below:

One set of records contains the Invoice No, Terms of Payment and Unit Price. Another set shows the Invoice No and the Date. Both sets of information contain different information about the invoices. To merge the two sets of records into one, we can use the vlookup function.

1. In cell C12, enter the formula (without inverted commas)

"**=VLOOKUP($A12,$A$2:$C$7,2,FALSE)**".

We have used a cell reference (**$A12**) in place of the value used in example #1.

2. In cell D12, enter the formula

"**=VLOOKUP($A12,$A$2:$C$7,3,FALSE)**".

3. Copy both formulas to row 13 until row 15.

4. Both sets of records are merged into one.

Notice that the invoice numbers are not in ascending
orders. It is not necessary to place then in ascending orders
because of the option "False" we placed in the formula
"=VLOOKUP($A12,$A$2:$C$7,3**,FALSE)**". The ascending order
requirement applies only when the value is omitted or entered as "**TRUE**"

There are 2 other functions that work the same way as the vlookup
funtion. They are
**HLOOKUP**
and LOOKUP. **HLOOKUP** refers to horizontal
lookup where the formula will lookup the value horizontally. Lookup
works in a very similar way to vlookup. The difference is in the way
the formula is written.

## New! Comments

Have your say about what you just read! Leave me a comment in the box below.