In almost every industry in the world, more often than not, you will be faced with comparing data. Whatever your department is, company data will need to be adjusted based on current needs.
In this post, we are going to show you how two tables are connected one another, just like in the real world. Let’s say you have these employee data and the assessment report of some employees who have finished doing the assessment. These two data are not usually together, one could be in the database, while the other could be sent by your superior who want to know more about the data. But this time to make it easy for learning purpose, we show it together in one sheet.
We can see that the table Assessment Report on the right doesn’t include employee names. This is very common in almost every company, since data like Assessment Report usually follows database many to one principal to save space and practicality in the database.
Now let’s say your manager sent you the Assessment Report in excel file, and you have your own employee data containing employee id, first name, and last name. Your manager wants to know who has conducted the assessment along with the test result, because he obviously cannot tell who they are only from the employee id.
You can filter the employee id individually and copy paste the last and first names, but when there are hundreds or thousands of data in the Assessment Report, this is when VLOOKUP comes in handy.
So basically this is what you need to type in the new last name field in Assessment Report data.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Or in our case, this is the exact formula we need to type.
=VLOOKUP(E4,$A$4:$C$16,2,FALSE)We can see that we filled the last parameter with FALSE. This is to indicate the formula to find the exact match instead of the closest match or approximate value. I you are new to this and want to only find the exact value like employee ID, make sure to always use FALSE or 0.
After checking that the first row of last name is indeed correct as it is in the employee data, you can copy and paste the cell to the other rows below it. Next, you can proceed with the first name.
The first name field is similar to last name, except the col_index_num needs to be set as 3, since the field First Name is in the third column that was selected in the dataset or table_array.
=VLOOKUP(E4,$A$4:$C$16,3,FALSE)
Notice that the only difference between last name and first name is the col_index_num, since it indicates the column number of table_array.
When you are done with your formula, hit enter and copy that first row of First Name and paste it into all rows below it.
In summary, I can guarantee you that if you work in a company or have your own business, you will most likely face this kind of situation where you need to extract data from other table. This VLOOKUP formula is indeed very simple yet very popular, and you definitely can make use of it very often. There are other advanced formulas like INDEX and MATCH to compare data like this. If you want to check it out, you can go to this post : Excel : Using INDEX and MATCH Formula to Perform Data Comparison.
