anonymous
|
In Excel, the VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.
let’s get started with a very simple example of what vlookup is all about. Suppose you had the following table:
Given a list of names in another part of the table (in this case, column H), you want to figure out what kind of animal it is:
Vlookup’s format looks like the following:
=vlookup(lookup value, table where values reside, column # where values are located, false)
Let’s look at each of these parts a bit closer.
The first thing that goes into the vlookup function is the thing you know (or are given) and that will be used to lookup other values. In this case, you have the names of the animals, so these are the things we know. In our example, they reside in column H, from cells H2 through H5. If we wanted to put the type of animal next to the name of the animal in column I (so I2 would correspond to the name of the animal in H2), we would insert the vlookup function there:
and put H2 as the first thing in our vlookup function:
Next, we need to know the location of the table where our values reside. These happen to be from cells A1 through B5 in this example, which we would highlight with our mouse to insert into the vlookup function. It’s very important that you include all the cells in the table.
Highlight the table with your mouse:
At the same time, the vlookup function automatically puts in the cells you’ve highlighted:
Next, we need the column number where the values are located. Always start with the first column (column A in this case) as #1 and count out to the right. In this example, the type of animal listed is in column 2, so that’s what we would need to insert in the vlookup function. Note that to use vlookup, your keys always have to be to the left of your values. (We’ll cover more of this in part II of the tutorial at a later date.)
Finally, the last attribute that vlookup takes is either “true” or “false”. I happen to always use “false”, and what this does is force vlookup to return the first exact value it finds. If that value isn’t found, then vlookup conks out and returns “#N/A”. Though we won’t use it in this example, if you select “true”, then rather than always looking for the exact value, vlookup will return the exact value if it exists, or the closest one to it that doesn’t exceed the key. (If you use “true”, you will need to sort your data in ascending order before using vlookup.)
Still with me? Again, this is what we would actually put in cells I2 if the names of the animals we have are located in cells H2 through H5:
=vlookup(H2, A1:B5, 2, false)
Once we close off the parenthesis and hit “Enter”, vlookup automatically calculates:
And so on. We would continue down each cell in column I that we needed. One thing to note is to make sure that the location of your keys and values is always selected correctly. Oftentimes, as you copy-and-paste formulas all around Excel, the location of the data will also move around relative to the cell. The easiest way to prevent this is to “lock” the range of the location; in this case, we would do so by using “$A$1:$B$5″ instead of “A1:B5″. This way, as we move down column I, say, to cell I2, A1:B5 doesn’t become A2:B6 but stays with the original range of data. This way, we can just copy what’s in cell I2 down the rest of the cells (from I3 through I5):
Finally, here’s our result, after making the “$” changes and copying and pasting the formula down the rest of the column:
This has been a really simple example of vlookup, and I’ll cover a bit more in part II with another example, still simple, but with slightly more data.
Although in practice, vlookup is usually used between Excel sheets and workbooks, once you understand this example (which has been done within a single sheet), using vlookup outside the same sheet shouldn’t be much harder.
 Posted 344 dy ago
|
