Excel Challenge 4 - Employee Dataset 2
This week, we will be completing a table of employee information using only 2 columns of data. In order to do this, we will be referring to a table in another sheet and using the VLOOKUP formula.
Download the data set and complete the tasks below.
- Using the EmpID column link to the "Employees - Dataset 1" table to fill the Full Name, DateofHire and DateofTermination columns.
- Finally, the Current Employee column should be filled using a formula format which returns 'Yes' for current employees and 'No' for former employees.
The end result should like the table below:
Stuck on the tasks?
+ Reveal Step-By-Step Instructions
In the Full Name, Date of Hire and Date of Termination columns, input a VLOOKUP formula.
In it's simplest form, this is what a VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE)
We are using the common denominator, EmpID, to identify each manager. It's located in cell B5. So this is what we should have typed so far into cell A5 of the tab 'Employees - DATASET Task 2:
We want to look for it in the range we built in the Task 1 tab, but we also need to lock the dataset, which is done by adding $ signs in front of the cell ranges. So, click the tab, and select the whole range, which is A4:I23. We need to amend that to $A$4:$I$23 to lock the dataset.
So now, we have:
=VLOOKUP(B5,'Employees - DATASET 1'!$A$4:$I$23,
In the data range in Task 1, Full Name, the value we want to return, is the fourth column from the left, so we add 4 to the formula, leaving us with:
=VLOOKUP(B5,'Employees - DATASET 1'!$A$4:$I$23,4,
Finally, we are looking for an exact match, so we use either FALSE or 0. That leaves us with our complete formula:
=VLOOKUP(B5,'Employees - DATASET 1'!$A$4:$I$23,4,FALSE)
We need to apply the same formula to the Date of Hire and Date of Termination columns.
The only thing we'll need to change is where we return a value from, as we don't want to populate these two columns with results from the fourth column in the dataset, which is Full Name.
Since Date of Hire and Date of Termination columns are the eighth and ninth columns respectively from left to right, our two formulas look like this:
=VLOOKUP(B5,'Employees - DATASET Task 1'!$A$4:$I$23,8,FALSE)
=VLOOKUP(B5,'Employees - DATASET Task 1'!$A$4:$I$23,9,FALSE)
If you haven't done it already, you can now just drag the formula down or autofill it, and this step is complete.
Sharpen your skills with our range of Excel courses designed to turn you from a table trainee to spreadsheet specialist here >
Enter Your Voucher Code Here
Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items.