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
Search JabSto ::




Custom Search