Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

Figure 8-14:
The formula in cell B2 returns the address in the
Data
range for the value in cell B1.

If the
Data
range occupies a single row, use this formula to return the address of the
Target
value:

=ADDRESS(ROW(Data),COLUMN(Data)+MATCH(Target,Data,0)-1)

If the
Data
range contains more than one instance of the
Target
value, the address of the first

occurrence is returned. If the
Target
value is not found in the
Data
range, the formula returns

#N/A.

Looking up a value by using the closest match

The VLOOKUP and HLOOKUP functions are useful in the following situations:

h
You need to identify an exact match for a target value. Use FALSE as the functionâ€™s

fourth argument.

h
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 that is less than the

lookup value is used.

But what if you need to look up a value based on the
closest
match? Neither VLOOKUP nor

HLOOKUP can do the job.

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.