In today’s Sunday Skills I will cover one of the most useful functions in Excel for the Compensation Manager.
Vlookup is like an advanced copy/paste function, which automatically copies for you only the information for data which is present in both files (the main file and the source file).
For example, imagine you are participating to a compensation survey and need to upload nationality in your survey data, but it is not present in the main file you are using for the survey. Problem : you have nationality, but in a different file which has a different number of employees in it because it was run with another effective date (so some were hired, others transferred in or out of the population, and some employees left).
If you use copy/paste you will use the employee name or employee ID as your common piece of information, and whenever it is present in both files you will copy the nationality in the main file where you need it. A regular copy/paste will take you a long time, it will be very boring to do, and you will increase the risk of typing mistake.
Vlookup does that automatically for you, in just a few seconds, and with no error risk. Let me explain to you how to do it :
- Identify the common denominator between the 2 files and make sure it is identical. In Compensation & Benefits, I recommend to use Employee ID because this number never changes. (Name spelling can vary from one file to another, female employees may change their name when getting married or divorced).
- Ensure both files are ordered in the same manner (eg : file is ordered by employee name from A to Z or employee ID smallest to largest)
- Assuming file A is where you want to import data (main file) and file B is where you are taking the data from (source file). In file B, count the “column number” of the data you want to import with respect to the common identifier. Column number 1 is the column which has the common identifier.
- For example : Employee ID is in column A, Nationality is in Column B, the “column number” of Nationality is 2.
- For example, Employee ID is in column C, Nationality in in column G, the “column number” of Nationality is 5
- Step 1: In the main file, click on the cell you want to fill, and select the vlookup function
- Step 2 : Fill in the blanks in the pop-up screen :
- Lookup value : is the common identifier, selected in file A / main file (where you want to import the data to)
- Table Array : is the selection of columns in file B (source file), from common denominator (Employee ID) to the data to import (Nationality). Simply go to file B and select the columns.
- Col index num : is the “column number” of the data you want to import.
- Range lookup : always type False
- Step 3 : Check that the tentative result makes sense to you (it will show just below the data you filled), then click OK
- Step 4 : Extend the function or copy it in the rest of the column in file A where you need to import the data
Let me know if you found this useful ! I am not really an Excel guru, but if you’d like me to explore specific topics, tell me about it in the comments section and I will try to figure it out for you.
Related posts :
Speak Your Mind