Chapter 14: Creating Formulas That Look Up Values
327
Looking up a value by using the closest match ........................................................
The VLOOKUP and HLOOKUP functions are useful in the following situations:l (^) You need to identify an exact match for a target value. Use FALSE as the function’s fourth
argument.
l (^) You need to locate an approximate match. If the function’s fourth argument is TRUE or
omitted and an exact match is not found, the next largest value less than the lookup value
is returned.
But what if you need to look up a value based on the closest match? Neither VLOOKUP nor
HLOOKUP can do the job.
Figure 14.15 shows a worksheet with student names in column A and 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 14.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 off-
set 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))}