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)
We have used a cell reference ($A12) in place of the value used in example #1.
2. In cell D12, enter the formula
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.