Saturday, November 1, 2008

When should you use the Vlookup function?

When you have a table with data, and you wish to retrieve specific information from it.
For example:You have an Excel table with student names and their grades.You wish that you could somewhere in the sheet type a student name, and immediately retrieve his grade (based on the data in the table).To achieve this, you can use "Vlookup": the function will look for the student’s name in the first column in the table, and will retrieve the information that is next to his name in the second column (which is his grade).
Another example:You have a big table consisting thousands of bank accounts.You wish to retrieve in another worksheet information regarding some specific accounts.To achieve this, you can type these specific account numbers, and put a Vlookup function next each one of them. The function will look for the account numbers in the big table, and retrieve relevant information from it.
The difference between “Exact match” and “Closest match”:
When you use the Vlookup function to retrieve information based on a student name or a bank account number, you cannot allow it to find something close or similar to “Jake”, or close to the account number “3647463”, but rather it has to find them exactly.
But sometimes you have a table that defines ranges, for example:$5,000 – “Small deposit”$20,000 – “Medium deposit”$100,000 – “Big deposit”$500,000 – “Huge deposit”
If you want the Vlookup to find the description for a deposit of $23,000 (which should retrieve “Medium deposit”), you will ask it to find a close match, and it will find $20,000.
This is very useful when dealing with dates. Look at the following table:4/1/2008 – Payment on time.6/1/2008 – Late with payment (small fine).8/1/2008 – Very late with payment (big fine).
If you would like to find what happens with a payment made on 7/14/2008, the function will relate it to the date 6/1/2008 and retrieve us “Late with payment (small fine)”.
Please note – the function will always retrieve the smaller closest match (in case it doesn’t find an exact match).