VLOOKUP Function Formula In Excel 2010 [Tutorial]

Desember 30, 2021 Posting Komentar

Excel VLOOKUP function can be used when you pauperization to look up the values in the particularized table and check it against the other data W. C. Fields for comparison purpose. VLOOKUP stands for Vertical lookup, ill-used to uncovering specific information from the datasheet. Past creating a sample table generally referred as lookup table you can extract info from it and equivalence it with the desired field to yield needed results. This post elaborates where you derriere use VLOOKUP subroutine.

Set up Excel 2010, and open a datasheet along which you want to utilise VLOOKUP function.

For instance, we suffer enclosed a student grading datasheet, containing fields; Name, Course, and Marks.

datasheet

Now we volition add new column Order, which will contain grades fastened away the students. Now for this, we will be using VLOOKUP function for looking up values from other hold over that contains sample data for grades.

Create deuce new columns containing marks range (classified in whatever order) and corresponding grades. you don't need to create them in a new worksheet, you nates place anywhere in the active datasheet as we just want to get values from it.

new table

Now in the Grade first row, we will write VLOOKUP operate. 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 apprais of the number which we will be sounding in the newly created table. We need to lookup the value 80 (Simon Marks field) in the newly created table. The close parameter, table_array defines the table we will embody referring to in our case it will be newly created table, containing ranges of marks and grades. col_index_num defines information from which column we want to elicit values to record, in our case it is the second column that contain grades range. [range_lookup] lets you to pick out an alternative either TRUE(approximately twinned of values) or FALSE (Exact matching of values).

We will write this function in Grade first course, it will go like this;

=VLOOKUP(C2,$F$3:$G$8,2,TRUE)

In the formula parameters, C2 is cell of column Marks which control marks secured past students, F3:G8 is the location of the newly created table, containing ranges of marks and grades (employ absolute referencing with $ preindication), 2 in the formula means that values from second column volition seem, and TRUE defines that we need some equate as we give birth enclosed ranges not exact values.

After evaluating formula, IT wish show grade A in Grade column As shown in the screen shot below.

vlook func 1

Now apply this serve over the whole Grade pillar, drag the asset sign towards the end of Grade newspaper column to practice it over, as exhibit in the screen iridescent below.

grade 1

Now we besides want to calculate the prize money for to each one student. for illustration, we assume the following criteria.

For form A $1000

For grade B+ $700

For grade B &600

For grade C+ $250

For grade D N/A

The criteria characterised contains the photographic value, so we leave glucinium making a small deepen in the parameters of the function. we will be choosing Faithlessly from [range_lookup] instead of TRUE as we want to show the exact equate.

D2 contains the mark secured aside students, sol it will train the time value in Grade chromatography column against the newly created columns, containing prize money criteria, equally shown in the screenshot at a lower place.

prize money 1

Now apply the subprogram in Prize Money column to view the treasure money won away each student. Straight off Eastern Samoa you can see in the screenshot below that away using VLOOKUP function it is easier to flavor up specific values for populating new fields by connecting different columns.

final 1

You can as wel check unconscious our previously reviewed guides on How to embed videos in Excel 2010 & Adding Outlook e-mail tool in Excel.