Page 104 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 5 INTERPOLATION 81

=MATCH(83119,$B$5:$B$16,0)

returns 4, the maximum value is the fourth value in the range. Second, use the
INDEX function to return the value in the same position in the array of months:

=I N DEX( $A$5:$A$16,4)
The specific values 83 119 and 4 can now be replaced by the formulas that
produced them, to yield the following "megaformula."

=INDEX( $A$5:$A$16, MATCH( MAX( $B$5: $B$16), $B$5:$B$16,0))
This example could not be handled using LOOKUP, since LOOKUP requires
that the lookup values (in this case in column B) be in ascending order.

Using Excel's Lookup Functions
to Obtain Values from a Two-way Table
A two-way table is a table with two ranges of independent variables, usually
in the leftmost column (x values) and in the top row 0, values) of the table; a two-
dimensional array of z values forms the body of the table. Figure 5-4 shows an
example of such a two-way table (see folder 'Chapter 05 Interpolation', workbook
'Interpolation 1', sheet 'Viscosity'), containing the viscosity of solutions of
ethylene glycol of various concentrations at temperatures from 0 to 250°F. The
table can also be found on the CD; the data extends down to row 32.
The desired z value from a two way table is found at the intersection of the
row and column where the x and y lookup values, respectively, are located.
Unlike in the preceding example showing the application of VLOOKUP, where
column-index-num was the value 2 (a value was always returned from column 2
of the array), we must calculate the value of column-index-num based on the y
lookup value. There are several ways this can be done. A convenient formula is
the following, where names have been used for references. Temp and Percent
are the lookup values, P-Row is the range $B$3:$K$3 that contains the y
independent variable and Table is the table $A$4:$K$32, containing the x
independent variable in column 1. The following formula was entered in cell M2
of Figure 5-5.

=VLOOKU P(Tem p,Table, MATCH( Percent, P-Row, 1 )+ 1,l)
The corresponding expression using references instead of names is
=VLOOKUP( M2, $A$4:$1$32, MATCH( N2, $B$3:$K$3,1 )+I, 1 )