How to use vlookup in excel 2007
If that value isn’t found, then vlookup conks out and returns “#N/A”. I happen to always use “false”, and what this does is force vlookup to return the first exact value it finds. (We’ll cover more of this in part II of the tutorial at a later date.)įinally, the last attribute that vlookup takes is either “true” or “false”. Note that to use vlookup, your keys always have to be to the left of your values. 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. Always start with the first column (column A in this case) as #1 and count out to the right. Next, we need the column number where the values are located. It’s very important that you include all the cells in the table.Īt the same time, the vlookup function automatically puts in the cells you’ve highlighted: 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. Next, we need to know the location of the table where our values reside. 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:Īnd put H2 as the first thing in our vlookup function: In our example, they reside in column H, from cells H2 through H5. In this case, you have the names of the animals, so these are the things we know. 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. Let’s look at each of these parts a bit closer. =vlookup(lookup value, table where values reside, column # where values are located, false)
Vlookup’s format looks like the following: 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: So, let’s get started with a very simple example of what vlookup is all about. Sadly, as most companies seem to rely on Excel as a poor-man’s database of sorts (a totally unscalable solution and prone to errors with every revision, but don’t get me started), once you know vlookup, it’s likely to become one of your most often used Excel functions. Excel also has a sister function called hlookup (h = horizontal) that can be used to look up values in rows. This function allows you to look up values in a table that are listed in column format (how most tables are laid out), given another value (let’s call this the “key”). The “V” in vlookup stands for “vertical” and “lookup” is pretty self explanatory. One of the most useful functions in Excel, called vlookup, does exactly that. If you use Excel much at your job, sooner or later, you’re bound to need to look up values in a table.