Microsoft Excel VLOOKUP Function

One of the most popular function of Microsoft Excel is VLOOKUP. Most of the users when first time use this function are pretty confused, as it have several options through which it can operate. This slide presentation is created to help the people interested in learning this wonderful function.

2. Introduction to the VLOOKUP Function
•Description
VLOOKUP function is used to search a value in
another table and if found return the
corresponding value of that table for the
specified column.
2

3. Types of VLOOKUP
•You can lookup values in one of the two
following ways:
–Range Lookup
•Range lookup is used when you want to search for
ranges , it will look for nearest minimum value from the
first column of the table i.e. if the table consists 70, 80,
90 … and you search for 75 it will destined to 70 as it is
the nearest and the minimum value
–Exact Lookup
•We use this kind of lookups when we need to seek
exact value
3

4. Syntax of VLOOKUP
•=VLOOKUP(which_value_to_search, in_which_range_to_search,
column_num, [is_it_a_range_lookup])
•The function have 4 parameters
•which_value_to_search: This is the value you want to search in
another table, it could be a value or a cell reference
•in_which_range_to_search : This is the range of that table in which
you want the value to be searched
•column_num : This is the column number which contains the value
you want to be returned if the required value found
•[is_it_a_range_lookup]: This is the final & optional parameter,
which indicates that if it is true (range of values) else if false
VLOOKUP will look for (exact value)
4

5. Practical Example of Range Lookup
•Requirements
–For performing range lookups you have to cater
the following two requirements:
•First column of the source table should contain the
values from which you want to search for
•The table in which you want to lookup value must be
sorted in ascending order by the first column
5

6. Practical Example of Range Lookup –
continued …
•Scenario - 1:
–We have a table in which
data of the salespersons
available along with
their sales, we provide
commissions to these
sales persons on the
basis of their sales which
are as given
Sales Percent
>=0 0%
>=5,000 5%
>=10,000 7%
>=20,000 10%
6

7. Practical Example of Range Lookup –
continued …
This is the
which_value_to_search
which we want to
look for in this case it
is 11,200
This is the
in_which_range_to_search where
we want to search. Note we have
put $ signs to make it absolute so
when we copy the formula
downwards or rightwards it don’t
change its value
This is the
column_number
for which value
to be returned
This parameter
basically defines
that
is_it_a_range_lookup
7
The answer of this example
will be 7% or 0.07 as it will seek
for 11,200 and the nearest
minimum value is 10,000
The corresponding value for 10,000
Is 7% so it will return 7%

8. Practical Example of Exact Lookup
•Scenario 1:
You have a table of employees which contains
the Employee_ID, First_Name, Last_Name &
their respective salaries, on another table you
want to enter the days they are present for
that specific month you need to calculate their
salary based on their basic salary listed in
master list.
8

9. Practical Example of Exact Lookup
This is the
which_value_to_search
which we want to
look for in this case it
is 104
This is the
in_which_range_to_search where
we want to search. Note we have put
$ signs to make it absolute so when we
copy the formula downwards or
rightwards it don’t change its value. As
it is from another sheet it also contains
sheet name MasterList
This is the
column_number
for which value
to be returned
This parameter
basically defines that
this is not a
is_it_a_range_lookup by
profiding a FALSE
9
Answer for this example will be 7,100 which
will than divided by 30 and multiplied by 20
which will return 4,733.33 (we have done
the multiplication & division as we want
Salary for the current month and the person
attended only 20 days this month

10. Conclusion
•VLOOKUP is used to look a value in another table
•There are two kinds of lookups first is Range and
other is Exact
•For range you need to sort the source table by its
first column in ascending order
•For any lookup first column of source table should
contain the values in which you want to lookup
Please visit:
www.exceladvise.com
For more
Please comment if you like it, or have any
suggestions.
10
Photo Courtesy – www.freedigitalphotos.net