Microsoft Office Tutorials and References

In Depth Information

Figure 8-15 shows a worksheet with student names in column A and data values in column B. Range B2:B20 is

named
Data.
Cell E2, named
Target,
contains a value to search for in the
Data
range. Cell E3, named
ColOffset,

contains a value that represents the column offset from the
Data
range.

Figure 8-15:
This workbook demonstrates how to perform a lookup by using the closest match.

The array formula that follows identifies the closest match to the
Target
value in the
Data
range and returns the

names of the corresponding student in column A (that is, the column with an offset of –1). The formula returns

Paul
(with a corresponding value of 6,800, which is the one closest to the
Target
value of 7,200).

{=INDIRECT(ADDRESS(ROW(Data)+MATCH(MIN(ABS(Target-Data)),

ABS(Target-Data),0)-1,COLUMN(Data)+ColOffset))}

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

The value in
ColOffset
can be negative (for a column to the left of
Data
), positive (for a column to the right of

Data
), or 0 (for the actual closest match value in the
Data
range).