Vlookup
Definition
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
- The value to be found in another sheet/file.
- The range where you want to find the value
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.
- The third field in a Vlookup is the column number of where the data is shown that must be returned.
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.
- The last field of a Vlookup is the field where you only type “false” or “true” in. We recommend only using the word “false”, as this would search for the exact word in your database. If you type in “true”, the Vlookup would search for the closest word to the one you’re looking for.
- Most people get the range wrong. If the first column selected is not the column in which the value exists, nothing can be returned because nothing can be found.
- Count the columns properly. If you did not count correctly, the wrong value will be returned.
- It helps by typing in 1, 2 and so on in the rows, and then counting the columns in that way. It sounds stupid but when you have 30 or 40 columns, it saves effort.
- The value to be found must be exactly the same. It may happen that there is a space extra in the included with your word in the database. Then it’s not same for excel and no value will be returned. If excel cannot find the value a #n/a will be returned.
- If you select a column which is not in your range a #ref will be returned. For example you range is from column A to C, but you want to return the 5th column, it cannot be found because your range consists only of 3 columns.
- Find missing data in your data...contact us for more info
- Find the row number automatically in the hlookp formula...contact us for more info
- Why Use Vlookup Instead of Complex Database Applications
- Vlookup - An Excel Tool to Manage Data
- Tips in Using Vlookup in Excel
- Maximizing Excel by Using Vlookup
- Implementation of Vlookup in Excel
- Applying Vlookup to Manage Students Records
Examples
Let’s finish our fruit and vegetables quickly.
So first we want to find the names
Nr |
Code |
Formula |
Result |
1. |
200 |
=VLOOKUP(B2,H:K,2,FALSE) |
Oranges |
2. |
300 |
=VLOOKUP(B3,H:K,2,FALSE) |
Grapes |
3. |
300 |
=VLOOKUP(B4,H:K,2,FALSE) |
Grapes |
4. |
400 |
=VLOOKUP(B5,H:K,2,FALSE) |
Tomatoes |
5. |
600 |
=VLOOKUP(B6,H:K,2,FALSE) |
Bananas |
6. |
100 |
=VLOOKUP(B7,H:K,2,FALSE) |
Apples |
7. |
200 |
=VLOOKUP(B8,H:K,2,FALSE) |
Oranges |
8. |
300 |
=VLOOKUP(B9,H:K,2,FALSE) |
Grapes |
9. |
400 |
=VLOOKUP(B10,H:K,2,FALSE) |
Tomatoes |
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.
Troubleshooting
The most common problems are as follows:
Things you can do with the vlookup formula
Articles:
<-----Back to index
Next topic----->
