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




Custom Search