How to use vlookup in Excel

How to use vlookup in Excel

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 :

First you need to check a few things out :
  1. 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).
  2. 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)
  3. 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.
    1. For example : Employee ID is in column A, Nationality is in Column B, the “column number” of Nationality is 2.
    2. For example, Employee ID is in column C, Nationality in in column G, the “column number” of Nationality is 5
Now let’s get into the 4 steps to use vlookup :
  • 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
That’s it ! In just a few clicks you have copied data, without error, that can cover hundreds or even thousands of employees.
I recommend doing a Copy/Paste special > Values of the imported data so that you can then “play” with your main file without messing the vlookup. You can then easily filter the file and focus only on the employees with “N/A” results : they are the ones which are into your main file but not into the source file (where you took the data from) so you’ll have to search the info in a different way for them.

For those of you who learn better through visuals, I have created a Powerpoint overview of the steps to follow.

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 :

Print Friendly, PDF & Email

Speak Your Mind

*