MS Excel is packed with formulas and functions that make our lives so much easier, every day. Excel functions expand beyond basic arithmetic functions. They save a lot of time when it comes to collating data from different sources.
One such formula in Excel which is most sought after is the Vlookup. Vlookup helps you collate data by comparing it from two different files.
Let’s say we have two Excel sheets, xl1 and xl2. Xl1 is a report that we are preparing based on the information present in a master excel sheet, xl2. We need to search for the data present in the master xl2 and collate it in the report xl1.
So, we are going to keep xl1 as current excel and xl2 as target excel. By keeping data from one column as reference from current xl1, we will look up for that data in the target xl2 and collate the data in xl1 to complete the report.
This is the formula that you need to type in the cell to call up Vlookup.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
It looks intimidating but let’s break it down to make our understanding easier.
Lookup_value: This is the value from your current Excel sheet that you would like to search in your target Excel sheet.
Table_array: Array is a collection of rows and columns. Table array is the data present in the rows and columns of the target Excel sheet.
Col_index: This is the column from the target Excel sheet; the data you would like to collate in the current Excel sheet.
Range_lookup: Range look up is a binary parameter where you would like to define the accuracy of the look up. 1 or true, will find the closest match assuming that the first column of the target table is sorted numerically or alphabetically. 0 or false, will look only for the exact match and return an error if the look up data is not found in the target Excel.
Still feel a bit staggered? Don’t worry, we will now look at an example and see the Vlookup formula at work.
Let’s say I want to make a month-wise sales report of various sales employees. I extract data from data bases that I have. I generate an Excel file with this data. We will call it sales report.
This is not good enough for me because I don’t remember my team with their employee IDs; I need their names. So I check with the HR and they give me a master file which has a list of all the employees’ names against their employee IDs. We will call it, master.
But this information is huge. I’ve shown only a few rows here in the screenshots but my report comes to about a 1,000 lines and the HR has a record of 100+ employees with their names and IDs.
I can pick each employee ID form my report and search for it individually in the HR report and copy and paste the name in my report. By doing this for a 100 employees, I will be spending nearly 2-3 hours, easily, assuming I only do this continuously for 2-3 hours, with no distractions.
Or, I can use the Vlookup formula and complete the task in less than 30seconds. Here’s how.
Step 1: Open files
Keep both the “sales report” and the “master” excel files open.
Step 2: Adding the name column
My sales report doesn’t have the name column. So I will add it first in between the Employee and the sales column as column c.
Step 3: Add Vlookup formula
In the first cell c2, of the name column, I will start typing the formula “=Vlookup(“
After opening the bracket, use the mouse to click on the cell b2, or just use the left arrow key to get the cursor to b2. You will see that the formula adds b2 as the lookup_value in cell c2. And b2 is now highlighted with a marching-ants cursor.
As per the formula, type a comma after selecting b2 and you will see that the formula is now asking for table_array.
Now here’s the tricky part. Use the mouse to navigate to the master file, or hit alt+tab. If you have only these two files opened, then alt+tab will give you the master file. But if you have some other windows open then you will have to hit alt+tab a few times before you get to the target file.
In any case, using the alt+tab keyboard shortcut is a lot faster than reaching out to your mouse every now and then.
Ok, so once you have the target file open which we currently call as the master file. You will see that the formula now appears on top of the sheet and is waiting for the table_array input.
Click and drag from cell a1 to the bottom most cell of column b. You will now see the table_array selection appears on top of the sheet. You will also see that it shows the total number of rows and columns selected as 71R X 2C.
The data we are looking for, which is the names of the employees is located in column b; which as per this array is column 2 and it also happens to be the last column of the array.
So, after selecting the table_array, type a comma and you will see that the formula is now asking for column index number, which is represented as col_index_num in the formula.
We need to index the data in column b2 from this file to our sales report file. Since the data we need to index is in b2 and since it’s the second column of the table_array, we will enter the col_index_num as 2. After that, type a comma and you will see that the excel now asks for range_lookup.
We most definitely want only the accurate values; so I am going to enter the range look up as (0) zero which v look up formula treats as FALSE.
Hit enter and you’re done with the Vlookup formula. Let’s take a look at the sales report sheet.
You see? Excel has searched for 28364 in the master sheet and taken the corresponding value of it from column b which is “Irfan” and populated that value against 28364 in my sales report sheet.
Step 4: Completion (this is where you will love vlookup)
Now to replicate what we did in the first cell of the name column to the rest of the cells, simply select the entire column and hit ctrl+d on your keyboard. You will see that the entire data has been populated.
And you can do this to 1000s or even 100,000s of rows of data. You will be able to impress everyone by completing it in just under 30 secs. Even 10, once you get the hang of the formula and the keyboard shortcuts.
Things to remember
- The data has to be in two different Excel files. Vlookup will not work when you try to do it with two sheets with a single Excel file.
- Your col_index_num data should be in the last column of your table_array file.
- Data which you will select for look up value has to be present in the target file if not, Excel will return an error in your formula.
That’s about it. Try it yourself. It might seem a bit tacky at the beginning, but you will thank yourself once you have it mastered.
Looking to sharpen your investment skills & financial functions in excel? Join our top-rated professional diploma in financial trading & investment today!