Microsoft Office Tutorials and References

In Depth Information

The formula in this section performs an operation that none of Excel's lookup functions can do. The array for-

mula that follows returns the value in a range named
Data
that is closest to another value (named
Target
):

{=INDEX(Data,MATCH(SMALL(ABS(Target-Data),1),ABS(Target-

Data),0))}

If two values in the
Data
range are equidistant from the
Target
value, the formula returns the first one in the list.

Figure 15-10 shows an example of this formula. In this case, the
Target
value is 45. The array formula in cell

D4 returns
48
— the value closest to 45.

Figure 15-10:
An array formula returns the closest match.

Returning the last value in a column