Microsoft Office Tutorials and References

In Depth Information

=LOOKUP(3,x,y)

In such a case, you probably want to interpolate. In other words, because the lookup value (3) is halfway

between existing
x
values (2 and 4), you want the formula to return a
y
value of 21.00 — a value halfway

between the corresponding
y
values 18.00 and 24.00.

Formulas to perform a linear interpolation

Figure 8-17 shows a worksheet with formulas in column B. The value to be looked up is entered into cell B1.

The final formula, in cell B16, returns the result. If the value in B3 is found in the
x
range, the corresponding
y

value is returned. If the value in B3 is not found, the formula in B16 returns an estimated
y
value, obtained us-

ing linear interpolation.

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.