Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

Figure 8-17:
Column B contains formulas that perform a lookup using linear interpolation.

Itâ€™s critical that the values in the
x range appear in ascending order. If B1 contains a value less

than the lowest value in
x or greater than the largest value in
x, the formula returns an error

value. Table 8-2 lists and describes these formulas.

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

Cell

Formula

Description

B3

=LOOKUP(B1,x,x)

Performs a standard lookup on the x range and returns the

lookedup value.

B4

=B1=B3

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

B6

=MATCH(B3,x,0)

Returns the row number of the x range that contains the matching

value.

B7

=IF(B4,B6,B6+1)

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

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

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.

B15

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

Calculates an adjustment factor based on the difference between

the x values.

B16

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

Calculates the estimated y value using the adjustment factor in B15.

Combining the lookup and trend functions

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

linear interpolation, uses the LOOKUP and TREND functions. One advantage is that it requires

only one formula (see Figure 8-18).