Page 100 - Excel for Scientists and Engineers: Numerical Methods

Chapter 5

Interpolation

Given a table of x, y data points, it is often necessary to determine the value
of y at a value of x that lies between the tabulated values. This process of
interpolation involves the approximation of an unknown function. It will be up
to the user to choose a suitable function to approximate the unknown one. The
degree to which the approximation will be "correct" depends on the function that
is chosen for the interpolation. A large number of methods have been developed
for interpolation; this chapter illustrates some of the most useful ones, either in
the form of spreadsheet formulas or as custom functions. Although some
interpolation formulas require uniformly spaced x values, all of the methods
described in this chapter are applicable to non-uniformly spaced values.

Obtaining Values from a Table

Since interpolation usually involves the use of values obtained from a table,
we begin by examining methods for looking up values in a table.

Using Excel's Lookup Functions
to Obtain Values from a Table
Excel provides three worksheet functions for obtaining values from a table:
VLOOKUP for vertical lookup in a table, HLOOKUP for horizontal lookup and
LOOKUP. The first two functions are similar and have virtually identical syntax.
The LOOKUP function is less versatile than the others but can sometimes be used
in situations where the others fail.
The function VLOOKUP(lookup-value, fable-array, column-index-num,
range-lookup) looks for a match between lookup-value and values in the
leftmost column of fable-array and returns the value in a specified column in the
row in which the match was found. The argument column-index-num specifies
the column from which the value is to be obtained. The column number is
relative; for example, a column-index-num of 7 returns a value from the seventh
column of table-array.
The optional argument range-lookup (I would have called this argument
match-type-logical) allows you to specify the type of match to be found. If

77