How to use VLOOKUP in Excel (part II) – some tips and advanced Excel tricks
Excel function tutorials
Last month, I wrote a short and simple example of how to use the VLOOKUP function in Excel and promised to follow up with a second part.
In this post, we’ll look at a slightly more complicated example and show a couple of tips and tricks for making VLOOKUP work correctly.
By the way, I’ve received a couple of comments and thanks for my previous post and just want to encourage readers to let me know if there are other examples of functions or situations they face that they need help with. They make a great source for future posts on this site :)
In our last example, we had a simple, two-column list of names and types of animals. In this post, we’ll take a look at a list of employee names and data, say, for calculating commissions for sales people. Here’s what our data looks like (on all images in this post, click to enlarge):
As you can see, we’re given employees’ last and first names, their base salaries, their bonus percentage, and the % of the year that they were employees. We’re also given a unique identifier in the form of an employee number. Let’s examine the data a bit further.
First, what we should notice is that there are employees with the same last and first names. There’s an Andrew Anderson as well as an Andrew Cobb. And a Penny and Jim Dee.
Remember that VLOOKUP will either return the first match it finds in a list. In this case, if we were to use VLOOKUP to lookup a list of last names or first names, VLOOKUP would always return Andrew Anderson’s data (if we were looking using the “First Name” field) or Penny’s data (if we were looking using the “Last Name” field).
So, what to do?
