Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

383

Determining whether a particular value appears in a range .....................................

To determine whether a particular value appears in a range of cells, you can choose Home ➪

Editing ➪ Find & Select ➪ Find and do a search of the worksheet. But you also can make this

determination by using an array formula.

Figure 17.3 shows a worksheet with a list of names in A5:E24 (named NameList). An array formula

in cell D3 checks the name entered into cell C3 (named TheName). If the name exists in the list of

names, the formula displays the text Found. Otherwise, it displays Not Found.

FIGURE 17.3

Using an array formula to determine whether a range contains a particular value.

The array formula in cell D3 is

{=IF(OR(TheName=NameList),”Found”,”Not Found”)}

This formula compares TheName to each cell in the NameList range. It builds a new array that con-

sists of logical TRUE or FALSE values. The OR function returns TRUE if any one of the values in

the new array is TRUE. The IF function uses this result to determine which message to display.