Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

391

Determining the closest value in a range .................................................................

The formula in this section performs an operation that none of Excel’s lookup functions can do.

The array formula 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 17.7 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 17.7

An array formula returns the closest match.

Returning the last value in a column .......................................................................

Suppose that you have a worksheet that you update frequently by adding new data to columns.

You may need a way to reference the last value in column A (the value most recently entered).

If column A contains no empty cells, the solution is relatively simple and doesn’t require an

array formula:

=OFFSET(A1,COUNTA(A:A)-1,0)