Excel 2010 Bible

Part II: Working with Formulas and Functions

326

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 cell 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 14.14 shows a

worksheet with a range of numbers that occupies a single column (named Data). Cell B1, which

contains the value to look up, is named Target.

FIGURE 14.14

The formula in cell B2 returns the address in the Data range for the value in cell B1.

The formula in cell B2, which follows, returns the address of the cell in the Data range that con-

tains the Target value:

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

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 isn’t found in the Data range, the formula returns #N/A.