One of the most popular query in Excel is how to look up a
value placed in one excel worksheet or workbook to another. So I am writing
this post to help all those guys who have the same question in mind. I will
explain a very helpful function in Excel called “VLOOKUP” with the help of the below example…
Suppose that you are working in an HR (Human
Resource) department and you are handling a data of around 1000 employees of
your company and the data goes like this ( see the screenshot below)…
As
you can see we have Employee ID, Employee Name, Designation and Monthly Salary
of all the employees in an excel worksheet. Now just think that if your collegue comes to you and gives you an Excel worksheet that contains a list of around 100
employee ID’s and he want to know the Monthly salary of those employees. Now if
you don’t know how to use VLOOKUP then probably you will have to find each
employee ID in the above database and then copy the respective Monthly salary
and paste it next to employee ID column given by your colleague and you will have to
repeat this process for 100 times….yes guys a HUNDRED TIMES!!! And this will
consume a lot of your time and energy, so to avoid this manual work we can use
an amazing Excel function called
VLOOKUP. Here “V” stands for Vertical i.e. if your data is in Vertical form then you can use this function and if you have data in horizontal form then Hlookup is the function you are looking for (again “H” stands for Horizontal). Now just check this out how to use VLOOKUP First you need to go to the workbook in which you want the result (in this example go to the workbook that contains 100 Emp. ID’s) then active the cell (in Monthly Salary column) next to the first Emp. ID and start writing the function ( see the screenshot below)…
VLOOKUP. Here “V” stands for Vertical i.e. if your data is in Vertical form then you can use this function and if you have data in horizontal form then Hlookup is the function you are looking for (again “H” stands for Horizontal). Now just check this out how to use VLOOKUP First you need to go to the workbook in which you want the result (in this example go to the workbook that contains 100 Emp. ID’s) then active the cell (in Monthly Salary column) next to the first Emp. ID and start writing the function ( see the screenshot below)…
First
type “=“ and then start writing VLOOKUP now here as you will open the
parenthesis you will see the syntax (everything written between the parenthesis
known as syntax) which contains some arguments (everything between the parenthesis
and separated by comma is known as an Argument). The first argument is
“lookup_value”. This means Excel is asking you to refer that value which you
want to lookup in the database. In our example the lookup value will be Emp.
ID, so we will refer it to A2.
The next argument is “table_array”. Table_array
is your database. So in this case table array will be all the data available in
our database workbook (Note: Make sure that whenever you choose a table_array
the column that contains the lookup value {Employee ID’s} and the result values
{Monthly Salary} should be covered). So just put comma after filling the
lookup_value and go to the other workbook which contains the database and
select all the data.
As
this database is available in other workbook you need not to freeze the rows
and column because excel by default will freeze the same (i.e.
providing “$” signs when referring the database) I’ll explain this reference
part in my other post but for now just remember one thing that providing “$”
sign will help us to freeze the range and when we will copy and paste the VLOOKUP function down in our result workbook this will not allow the database
range selection to go down which will help us to get the correct result. But suppose if
both database and result sheets would be in the same workbook then we would
require to put the “$” sign manually.
Now
the next argument is “col_index_num”. This argument is asking for that column
number of your database worksheet which contains your ultimate result (in this example,
Monthly salary column). So the Monthly Salary column was the 4th
column of the database (Note: do not
count the column number as column A, B, C and so on….. rather count the column
as your “Table_array” i.e. column “Emp. ID” as column 1, “Emp. Name” as column
2, “Designation” as column 3 and “Monthly Salary” as column 4). So the column
number in our example will be column 4.
Last
argument is “Range_lookup”. You noticed that this argument is in square
brackets it means that this argument is optional. Now let me dedicate few words to this, it has two options First
option is True or Approximate match i.e. If TRUE or omitted, an exact or approximate
match is returned. If an exact match is not found, the next largest value that
is less than lookup_value is returned. The values in the first column of
table_array must be placed in ascending sort order; otherwise, VLOOKUP may not
give the correct value. Second option is Flase or exect match i.e. If FALSE,
VLOOKUP will only find an exact match. In this case, the values in the first
column of table_array do not need to be sorted. If there are two or more values
in the first column of table_array that match the lookup_value, the first value
found is used. If an exact match is not found, the error value #N/A is
returned. So in this case where we know that Emp. ID is unique so we can use False or 0.
Now you can just close the parenthesis and press enter and
you will find the value you were looking for if that is available in you
database. And after that just copy and paste the same below for all 100 Emp.
ID’s and that is it……