|Home - Contact Us - Courses - Learn online free - Assessment - Manuals - Sitemap - Articles - Company profile - Newsletters - Tips|
When you want to exract data in your current worksheet, but the data is on another worksheet or another file, the Vlookup formula helps you to find that data. For example, if you got all the customers names in one sheet, but you would like to send them all birthday cards, but the customers birthdates are on another sheet. You can then find the customer’s name on another sheet and return the birth date to your sheet if the birth date is on the same row as the customer name on the other sheet.
The makeup of the formula
The basic Vlookup comprises of 4 fields
REMEMBER! The way the formula works is that it will always search for value in the left column of the range you select. Let’s show an example:
In this example we selected from column c to d. In a Vlookup, if you selected these columns (Range) the value to be found will ONLY be searched for in column c.
Easy?, Let’s see one more
Here we selected H6 to N21. So what is our range? It’s H6:N21. Where will the Vlookup search? ONLY in column H from row 6 to 21.
This is why we select more than one column at once, so that we can extract the data, next to field to be found. In this field we must insert a number. This number represents the column in which our data is. You can insert a formula in here to calculate the number for you. The result must however be a number. The first column is always the column in which we search for the data
Let’s see an example:
Let’s say we are a small shop. We sell fruit and vegetables. In B2:B10 we have codes for our fruit and vegetables that we sold yesterday. We would like to know what the names are. So where would we search? In column H1:H6. We must return the names in column I2:I6 (the second column of our range selected). The column where we search is always the first one (column H), Column I is the second one, J is the third and K is the fourth and so on. So, if we want the names, the third field in our Vlookup would be the number 2, which represents the 2nd column in our range we selected.
In our database/range (H1:K6) we have the prices for our goods as well. How can we get these? The third field must then be 4, to extract the 4th column in our range. Always make your range as big as possible, if you want to extract other data later on.
Let’s finish our fruit and vegetables quickly.
See below how this would look in the formula bar.
The value we are looking for in number 1 is 200, which is in A2.
The range where we want to look for starts in column H, thus the blue, but we want to make the range rather big, so we select until K, to save our self some problems if we want to extract other data later.
We want to return the name of the code which is in the second column of our range, so thus the third field would be 2
The fourth field is always false, for safety’s sake. (Remember if you forget to put the fourth field in, it will automatically be true.
The most common problems are as follows:
Things you can do with the vlookup formula
<-----Back to index
|Home | Testimonials | Terms & Conditions | Contact Us | Courses | Manuals | Sitemap | Articles ||