Tips for Excel, Word, PowerPoint and Other Applications
Introduction to VLOOKUP
Why It Matters To You
VLOOKUP is probably one of the most important functions you need to learn in Excel. Simply put, VLOOKUP (e.g., Vertical LOOKUP), and its cousins HLOOKUP and LOOKUP allows you to search, vertically, for a value in a table and return a corresponding value. It's one of the more useful functions in Excel because of its utility in combining data from multiple sources or transforming data.
It's just as important to understand how Excel works through the lookup operation because it has significant implications on how your data is laid out. So without further ado ...
How To ...
Here are the 4 basic arguments of a VLOOKUP function. The fourth argument, Range_Lookup, is optional and only required if you only want to find exact matches, which we'll talk about a little further down. TIP: When you specify your TABLE_ARRAY, you don't have to select a discrete range of cells, you can select entire columns, especially useful if you either aren't sure of the size of your data table or you know it's going to change.
Another explanation
Laid out like this, it's not that hard to follow the train of logic when a VLOOKUP is calculated. Let's talk about other specifics and considerations when using VLOOKUP. I'll be using an example file, vlookup_basic.xls.
When to Return an Exact Match vs The Closest Value.
By default, VLOOKUP tries to return an exact match, or if it cannot find one, the closest value starting from the top of the table and working its way down the list. Because of the way Excel determines near matches, it's important that your list is sorted in descending order. This has structural implications on your underlying data table, especially if it a) needs to be sorted by other criteria or b) other considerations preclude sorting.
Typically, you'll want to use exact matches when your Lookup_Values belong to a discrete set, for example, a list of names. You'll want to use nearest match when your Lookup_Values form a continuous set, for example a range of dates.
Example 1 and 2 are the same list, one sorted, one not. Try entering different values into the yellow cell and see what happens in each example. As you can see, if your Lookup_Values are not sorted, then you will generate a lot of false answers.
So when is this useful? Example 3 is an example, similar to the Treasury scorecard, where performance is being scored. Depending on the % performance against a goal, each department is given a discrete score. The way the scoring bands are set up lend themselves very well to the way VLOOKUP defaults to returning values.
Try using the scroll bar in the example file to change the LOOKUP value and you will see that the score changes only when the performance hits a new scoring band.
Just to complete the thought, I almost always use VLOOKUP with exact matches, though you have to be ready to deal with the error message, when they occur, because they can corrupt calculations. For example, if you are looking up employee salary figures and you can't find someone, resulting in a #NA error message, your sum of employee salaries may also result in an error. We'll discuss this in another article.
Using Exact Matches
If you specify the lookup_range = FALSE (or 0), then you're telling Excel to only return exact matches and if no exact match is to be found, then to return a #NA error.
This is actually quite useful information. For example, if I'm trying to match 2 lists of employee ID numbers, then I would ask VLOOKUP to find all of the exact matches. I can use then an ISERROR/IF combination to convert any errors into a message like "No longer at works here", then use a COUNTIF to calculate how many people on the list are no longer at the firm.
FOR ADVANCED USERS
Using Variables in a VLOOKUP
VLOOKUPs are a lot more flexible than most people realize. Typically, you only see the first argument (Lookup_value) as the variable because people are trying to match a number of values against a vertical list.
In fact, set up correctly, you can set your VLOOKUP to search multiple tables on multiple worksheets, and return different values based on what you specify for the column index number.
The following are some examples of what you can do to enable the use of dynamic variables for the other 3 arguments (which we may address in future Tips):
Hope this made perfect sense to everyone
Notes
| Last updated | 9/3/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | VLOOKUP and IF/ISERROR Combos |