Microsoft Office Tutorials and References
In Depth Information
Specialized Lookup Formulas
Figure 8-13: This workbook performs a lookup by using information in two columns (D and E).
The lookup table contains automobile makes and models, and a corresponding code for each.
The worksheet uses named ranges, as shown here:
F2:F12
Code
B1
Make
B2
Model
D2:D12
Makes
E2:E12
Models
The following array formula displays the corresponding code for an automobile make and model:
{=INDEX(Code,MATCH(Make&Model,Makes&Models,0))}
This formula works by concatenating the contents of Make and Model and then searching for this
text in an array consisting of the concatenated corresponding text in Makes and Models .
Determining the address of a value within a range
Most of the time, you want your lookup formula to return a value. You may, however, need to
determine the cell address of a particular value within a range. For example, Figure 8-14 shows a
worksheet with a range of numbers that occupy a single column (named Data ). Cell B1, which
contains the value to look up, is named Target.
The formula in cell B2, which follows, returns the address of the cell in the Data range that
contains the Target value:
=ADDRESS(ROW(Data)+MATCH(Target,Data,0)-1,COLUMN(Data))
 
Search JabSto ::




Custom Search