VLOOKUP in Excel by malj


									                                          VLOOKUP in Excel

VLOOKUP is an Excel function which enables you to use vertical “lookup tables” in Excel. Its use
is documented with examples in the Excel HELP files. Search for VLOOKUP to find it. The
function HLOOKUP does the same thing for tables organised in rows instead of columns.
VLOOKUP and HLOOKUP are very useful for extracting items from tables depending on the value
in some cell. The following examples show a simple use in converting marks to grades and a more
sophisticated use that allows linear interpolation of physical properties.

Matching of grades to mark ranges
One use of VLOOKUP is to convert marks to grades.
           A       B          C          D                   E
  1 Marks to grade conversion
  2     Marks    Grade
  3            0   E
  4           35   D
  5           50   C
  6           58  C+
  7           62  B-
  8           66   B
  9           70  B+
 10           75  A-
 11           80   A
 12           85  A+
 14           79  A-

The formula in B14 is:        =VLOOKUP(A14,Marks,2)
This takes the mark entered in A14, searches the range named “Marks” (which has been defined as
the cell range A3:B12), and returns the value from column 2 in “Marks” in the row with the highest
number in column 1 less than the value in A14.

Use of VLOOKUP for linear interpolation.

Consider the problem of estimating y for x = 2.6 given a set of values of y for x = 1, 2, 3, 4, 5.
Using linear interpolation, the result is calculated by:

y2.6  y2   y3  y2  
                            2.6  2.0                (1)
                            3.0  2.0

See figure 1.

ENGR250, P J Jordan                                1                                           18/05/10
                                             x y2
                         y2.6                                 y3               x
                                                          x            x

                                    1        2            3        4       5
                      Figure 1. Linear interpolation between available values.

If we have a property, for example Prandtl number, for certain values of temperature, we can find
the Prandtl number for intermediate temperatures in the same way. The independent variable
(temperature) values are placed in column 1, column 2 contains the differences between values in
column 1. Each remaining pair of columns in the table contains the values of a property of interest
at the given temperatures and the differences in the successive property values.

The spreadsheet printout on page 3 gives physical properties of air and shows how to interpolate for
one property for any given temperature.

In the example, the temperature for which Prandtl number is required is in cell B17. The Prandtl
number is calculated with the formula in B18:


Here “properties” is the name that has been given to the range of cells A9:L13.

VLOOKUP($B$17,properties,11) is the value in column 11 of the table “properties” in the row
containing the highest number in column 1 of “properties” which is less than the number in B17.
This is the value in cell K11. It corresponds to y2 in equation (1).

VLOOKUP($B$17,properties,12) is the value in the same row and column 12 of “properties”, the
value in cell L11. It corresponds to (y3-y2) in (1).

($B$17-VLOOKUP($B$17,properties,1)) gives the temperature in B17 minus the highest
temperature less than B17 in column 1 of “properties”. It is the value in cell A11. This corresponds
to the (2.4-2.0) in equation (1).

VLOOKUP($B$17,properties,2) is the size of the temperature interval in which the required
temperature lies. It is the value in cell B11 and corresponds to (3.0-2.0) in equation (1).

The name “properties” has been given to the cell range A9:L13 by:

       Selecting the range A9:L13

ENGR 250, P J Jordan                                2                                    5/18/2010
        INSERT/NAME/DEFINE and enter the name “properties” in the top box in the window
that has opened.

             Click OK

Now if you select the range A9:L13, you will see its name displayed at the left end of the formula

In place of the name “properties”, the cell range could simply be specified as A9:L13.

         A         B           C          D          E       F         G                 H            I            J     K        L        M

 2   The Use of VLOOKUP for linear interpolation
 3 P J Jordan                 14/03/01
 4 Properties of air at one atmosphere
 6   Temperature                 Density          Specific heat Viscosity                          Conductivity        Prandtl
 7 (K)    delta             (kg/m^3) delta       (J/kg/K) delta kg/m/s          delta            W/m/K delta           number delta
 9        200          50    1.7684    -0.3556    1006.1 -0.8        1.33E-05      2.701E-06      0.01809    0.00418    0.739   -0.017
10        250          50    1.4128    -0.2354    1005.3 0.4         1.60E-05      2.472E-06      0.02227    0.00397    0.722   -0.014
11        300          50    1.1774    -0.1684    1005.7 3.3         1.85E-05      2.288E-06      0.02624    0.00379    0.708   -0.011
12        350          50    1.0090    -0.1264    1009.0 5.0         2.08E-05     0.00000211      0.03003    0.00362    0.697   -0.008
13        400                0.8826               1014.0             2.29E-05                     0.03365               0.689
15                                    The delta columns are calculated for example by:
16                                    In B9:     =A10-A9
17   Tfilm1        390
18   Pr         0.6906                Where the formula in B18 is:
20   =VLOOKUP($B$17,properties,11)+VLOOKUP($B$17,properties,12)*($B$17-VLOOKUP($B$17,properties,1))/VLOOKUP($B$17,properties,2)
22   The name properties has been given to the cell range A9:L13 by this procedure:
23   1.       Select the range A9:L13
24   2.       INSERT/NAME/DEFINE, typing properties in the top box in the window which has opened, the clicking OK.
26   Now if you select the range A9:L13 you will see its name displayed at the left hand end of the formula bar.
28       =VLOOKUP($B$17,properties,11)+VLOOKUP($B$17,properties,12)*($B$17-
29       VLOOKUP($B$17,properties,1))/VLOOKUP($B$17,properties,2)

ENCH271, P J Jordan                                                         3                                                         5/18/2010

To top