Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

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
Leslie
(with a matching value of 8,000, which is the one closest to

the
Target
value of 8,025).

{=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).

To understand how this formula works, you need to understand the INDIRECT function. This

function’s first argument is a text string in the form of a cell reference (or a reference to a cell that

contains a text string). In this example, the text string is created by the ADDRESS function, which

accepts a row and column reference and returns a cell address.

Looking up a value using linear interpolation

Interpolation
refers to the process of estimating a missing value by using existing values. For an

illustration of this concept, see Figure 8-16. Column D contains a list of values (named
x) and

column E contains corresponding values (named
y).