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?
In this example, we’re lucky to have a unique identifier in the form of “Employee Number”. Each number is assigned only once to the employee, so this field would be a safe one to use for VLOOKUP. The only problem is that it’s located all the way at the end of the data, to the right of all the other fields. Remember that VLOOKUP has another criteria: whatever field you’re using to look up other data has to be to the left of all the other fields.
The easiest way to accomplish this is to insert a column to the left of “Last Name” (Column A) and copy-and-paste the “Employee Number” column there. Here’s how that would look, step by step:
Step 1: Select column F, where “Employee Number” data is located:
Step 2: Right-click on the mouse:
Step 3: Select “Copy” from the menu:
All of column F is now highlighted in a dotted line:
Step 4: Highlight column A:
Step 5: Right-click on the mouse once more:
Step 6: Select “Insert Copied Cells” on the menu
Step 7: The cells from column F are now copied over to column A, and everything is shifted over one column:
Now, employee numbers appear in both column A and G. Hitto get rid of the highlight around column G.
We’re now good to go!
By the way, if you had not had unique identifiers like employee numbers readily available, you could potentially use the CONCATENATE or “&” function in Excel to “create” unique identifiers. CONCATENATE is a function that just munges two fields together. In this case, creating a unique identifier out of concatenating last name and first name would probably work.
Back to the tutorial. Suppose we had a second sheet that had a list of employee numbers for the four employees who had worked less than 100% during the year, and we wanted to calculate their bonuses for the year. Notice we swapped first and last name orders in this sheet and put the employee numbers in a different order:
We just want to fill in the data from the other source (possibly from another Excel sheet or workbook) in order to do the calculation. Here, I’ve left the original data in “Sheet1″ and am pulling the data into “Sheet2″.
What would we put into cell B2 in order to pull the data correctly? In the example in the last post, we only had two columns of data. In this example, we have seven, after copying over the employee numbers into the first column.
For the “First Name” column, we would be pulling data from column 3 in our data set (not column 2, which contains last names). So here’s what the VLOOKUP would look like for cell B2:
=VLOOKUP(A2,Sheet1!A1:G8,3,FALSE)
And here’s the excel step-by-step:
Step 1: Insert the VLOOKUP function by typing “=vlookup(” in cell B2:
Step 2: We’re looking up Employee Numbers, which are located in the previous column, so we put in A2 for cell B2:
Put in a “,” after this to move on to the next input for VLOOKUP called “table_array”.
Step 3: Now we need to highlight the area where all the data resides:
Put in a “,” after this to move on to the next input for VLOOKUP, called “col_index_num”.
Step 4: Remember that in this case, we need to reference column #3, where first names are located. We always start with the lookup value as column #1 and count toward the right.
Put in a “,” after this to move on to the final input for VLOOKUP.
Step 5: Finally, we want to put in “false” as the final input into VLOOKUP to tell it to look for exact matches.
Now close off the parenthesis to VLOOKUP, and the cell is automatically populated with the data we need.
The key now is to populate the rest of the cells. Can you figure out how to do this? One way would be to go through each cell and repeat the steps above. For example, to populate cell C2, we would write:
=VLOOKUP(A2,Sheet1!A1:G8,2,FALSE)
and so on, referencing each column where the data resides. (“Salary” resides in column 4, “bonus” in column 5, etc.) Another way would be to use Excel’s anchoring mechanism so that we could copy and paste formulas a bit more efficiently.
For example, for the rest of the cells under “First Name”, what we could do is write the following instead in B2:
=VLOOKUP($A2,Sheet1!$A$1:$G$8,3,FALSE)
What putting a “$” sign does in front of cell coordinates is to “lock” them in place. By putting $A2 instead of A2 in the first input section, we lock “A” in place (because all our employee numbers are in column A) and let the “2″ change as we go down the row.
By putting “$A$1:$G$8″ instead of “A1:G8″ as we originally had, we lock in the entire A1 to G8 cells in place and keep that section “locked” no matter where we put the formula.
If we then copy the formula down to cells B3 through B5, we don’t have to retype the formula each time. Similarly, you can copy the formula across each row, making sure to just change each column number so that you’re pulling the right data.
Here’s what the finished table would look like:
And here’s what the final column, which is just the total bonus calculation, would look like if we assumed that bonuses equaled salary * bonus * % of year worked:
In this example, we populated a new table in a new sheet with data from a separate sheet. But keep in mind one of the powerful things of VLOOKUP is that with a unique identifier such as “Employee Number”, what we could do is create an entirely new table with elements from multiple other tables that each contain “Employee Number”. For example, salary information might be stored in one place, and employee names in another. By using VLOOKUP to lookup employee numbers from each table, we could create one table that contains all information at once.
This has been a pretty lengthy example that’s actually covered a lot of different Excel tips, so we’ll stop here. If you have any specific questions or examples that you wouldn’t mind sharing (of course, all data would be disguised and I’d get your permission first), I’d be happy to answer them by creating a post with your example in the center.
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?
In this example, we’re lucky to have a unique identifier in the form of “Employee Number”. Each number is assigned only once to the employee, so this field would be a safe one to use for VLOOKUP. The only problem is that it’s located all the way at the end of the data, to the right of all the other fields. Remember that VLOOKUP has another criteria: whatever field you’re using to look up other data has to be to the left of all the other fields.
The easiest way to accomplish this is to insert a column to the left of “Last Name” (Column A) and copy-and-paste the “Employee Number” column there. Here’s how that would look, step by step:
Step 1: Select column F, where “Employee Number” data is located:
Step 2: Right-click on the mouse:
Step 3: Select “Copy” from the menu:
All of column F is now highlighted in a dotted line:
Step 4: Highlight column A:
Step 5: Right-click on the mouse once more:
Step 6: Select “Insert Copied Cells” on the menu
Step 7: The cells from column F are now copied over to column A, and everything is shifted over one column:
Now, employee numbers appear in both column A and G. Hit
We’re now good to go!
By the way, if you had not had unique identifiers like employee numbers readily available, you could potentially use the CONCATENATE or “&” function in Excel to “create” unique identifiers. CONCATENATE is a function that just munges two fields together. In this case, creating a unique identifier out of concatenating last name and first name would probably work.
Back to the tutorial. Suppose we had a second sheet that had a list of employee numbers for the four employees who had worked less than 100% during the year, and we wanted to calculate their bonuses for the year. Notice we swapped first and last name orders in this sheet and put the employee numbers in a different order:
We just want to fill in the data from the other source (possibly from another Excel sheet or workbook) in order to do the calculation. Here, I’ve left the original data in “Sheet1″ and am pulling the data into “Sheet2″.
What would we put into cell B2 in order to pull the data correctly? In the example in the last post, we only had two columns of data. In this example, we have seven, after copying over the employee numbers into the first column.
For the “First Name” column, we would be pulling data from column 3 in our data set (not column 2, which contains last names). So here’s what the VLOOKUP would look like for cell B2:
And here’s the excel step-by-step:
Step 1: Insert the VLOOKUP function by typing “=vlookup(” in cell B2:
Step 2: We’re looking up Employee Numbers, which are located in the previous column, so we put in A2 for cell B2:
Put in a “,” after this to move on to the next input for VLOOKUP called “table_array”.
Step 3: Now we need to highlight the area where all the data resides:
Put in a “,” after this to move on to the next input for VLOOKUP, called “col_index_num”.
Step 4: Remember that in this case, we need to reference column #3, where first names are located. We always start with the lookup value as column #1 and count toward the right.
Put in a “,” after this to move on to the final input for VLOOKUP.
Step 5: Finally, we want to put in “false” as the final input into VLOOKUP to tell it to look for exact matches.
Now close off the parenthesis to VLOOKUP, and the cell is automatically populated with the data we need.
The key now is to populate the rest of the cells. Can you figure out how to do this? One way would be to go through each cell and repeat the steps above. For example, to populate cell C2, we would write:
and so on, referencing each column where the data resides. (“Salary” resides in column 4, “bonus” in column 5, etc.) Another way would be to use Excel’s anchoring mechanism so that we could copy and paste formulas a bit more efficiently.
For example, for the rest of the cells under “First Name”, what we could do is write the following instead in B2:
What putting a “$” sign does in front of cell coordinates is to “lock” them in place. By putting $A2 instead of A2 in the first input section, we lock “A” in place (because all our employee numbers are in column A) and let the “2″ change as we go down the row.
By putting “$A$1:$G$8″ instead of “A1:G8″ as we originally had, we lock in the entire A1 to G8 cells in place and keep that section “locked” no matter where we put the formula.
If we then copy the formula down to cells B3 through B5, we don’t have to retype the formula each time. Similarly, you can copy the formula across each row, making sure to just change each column number so that you’re pulling the right data.
Here’s what the finished table would look like:
And here’s what the final column, which is just the total bonus calculation, would look like if we assumed that bonuses equaled salary * bonus * % of year worked:
In this example, we populated a new table in a new sheet with data from a separate sheet. But keep in mind one of the powerful things of VLOOKUP is that with a unique identifier such as “Employee Number”, what we could do is create an entirely new table with elements from multiple other tables that each contain “Employee Number”. For example, salary information might be stored in one place, and employee names in another. By using VLOOKUP to lookup employee numbers from each table, we could create one table that contains all information at once.
This has been a pretty lengthy example that’s actually covered a lot of different Excel tips, so we’ll stop here. If you have any specific questions or examples that you wouldn’t mind sharing (of course, all data would be disguised and I’d get your permission first), I’d be happy to answer them by creating a post with your example in the center.
No comments:
Post a Comment