VLOOKUP Function In Microsoft Excel 2010
Excel VLOOKUP function can be used
when you need to look up the values in the specific table and check it against
the other data fields for comparison purpose. VLOOKUP stands for Vertical
lookup, used to find specific data from the datasheet. By creating a sample
table generally referred as lookup table you can extract info from it and
compare it with the desired field to yield required results. This post
elaborates where you can use VLOOKUP function.
Launch Excel 2010, and open a datasheet on which you want to apply VLOOKUP
function.
For instance, we have included a student grading datasheet, containing
fields; Name, Course, and Marks.
Now we will add new column Grade, which
will contain grades secured by the students. Now for this, we will be using
VLOOKUP function for looking up values from other table that contains sample
data for grades.
Create two new columns containing marks range (sorted in any order) and
corresponding grades. you don’t need to create them in a new worksheet, you can
place anywhere in the existing datasheet as we just want to get values from it.
Now in the Grade first row, we will write
VLOOKUP function. The syntax of this function is
VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup] )
The first parameter of the formula lookup_value defines
the value of the number which we will be looking in the newly created table. We
need to lookup the value 80 (Marks field) in
the newly created table. The next parameter, table_array defines
the table we will be referring to in our case it will be newly created table,
containing ranges of marks and grades. col_index_num defines
data from which column we want to extract values to show, in our case it is the
second column that contain grades range. [range_lookup]lets
you to choose an option either TRUE(approximately matching of values) or
FALSE (Exact matching of values).
We will write this function in Grade first
row, it will go like this;
=VLOOKUP(C2,$F$3:$G$8,2,TRUE)
In the formula parameters, C2 is cell of
column Marks which contain marks secured by students, F3:G8 is the location of the newly created
table, containing ranges of marks and grades (use absolute referencing with $
sign), 2 in the formula means that values from second
column will appear, and TRUE defines that we
need approximately match as we have included ranges not exact values.
After evaluating formula, it will show grade A in Grade column as
shown in the screen shot below.
Now apply this function over the whole Grade column,
drag the plus sign towards the end of Gradecolumn to apply
it over, as show in the screen shot below.
Now we also want to calculate the prize money for each student. for
instance, we assume the following criteria.
For grade A $1000
For grade B+ $700
For grade B &600
For grade C+ $250
For grade D N/A
The criteria defined contains the exact value, so we will be making a
small change in the parameters of the function. we will be choosing FALSE from
[range_lookup] instead of TRUE as we want to show the exact match.
D2 contains the grade secured by students,
so it will check the value in Grade column
against the newly created columns, containing prize money criteria, as shown in
the screenshot below.
Now apply the function in Prize Money column
to view the prize money won by each student. Now as you can see in the
screenshot below that by using VLOOKUP function it is easier to look up
specific values for populating new fields by connecting different columns.
