excel courses, excel training

advance excel courses

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:

vlookup1

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

vlookup2

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:

vlookup3

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.
  • 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.

    vlookup6

    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:

    • 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.

    Things you can do with the vlookup formula

    Articles:

    <-----Back to index

    Next topic----->

     

     

     

     

     

     

Home | Testimonials | Terms & Conditions | Contact Us | Courses | Manuals | Sitemap | Articles |