Microsoft Office Tutorials and References

In Depth Information

Table 8-2: Formulas for a Lookup Using Linear Interpolation

Cell

Formula

Description

Performs a standard lookup on the
x
range and returns the

looked-up value.

B3

=LOOKUP(B1,x,x)

Returns TRUE if the looked-up value equals the value to be

looked up.

B4

=B1=B3

Returns the row number of the
x
range that contains the

matching value.

B6

=MATCH(B3,x,0)

Returns the same row as the formula in B6 if an exact match

is found. Otherwise, it adds 1 to the result in B6.

B7

=IF(B4,B6,B6+1)

B9

=INDEX(x,B6)

Returns the
x
value that corresponds to the row in B6.

B10
=INDEX(x,B7)

Returns the
x
value that corresponds to the row in B7.

B12 =LOOKUP(B9,x,y)

Returns the
y
value that corresponds to the
x
value in B9.

B13 =LOOKUP(B10,x,y)

Returns the
y
value that corresponds to the
x
value in B10.

=IF(B4,0,(B1-B3)/

(B10-B9))

Calculates an adjustment factor based on the difference

between the
x
values.

B15

Calculates the estimated
y
value using the adjustment factor

in B15.

B16 =B12+((B13-B12)*B15)

Combining the lookup and trend functions

Another slightly different approach, which you may find preferable to performing lookup using linear interpola-

tion, uses the LOOKUP and TREND functions. One advantage is that it requires only one formula (see Figure

8-18).