Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

Figure 15-3:
Using an array formula to determine whether a range contains a particular value.

Yet another approach uses the COUNTIF function in a non-array formula:

=IF(COUNTIF(NameList,TheName)>0,”Found”,”Not Found”)

Counting the number of differences in two ranges

The following array formula compares the corresponding values in two ranges (named
MyData

and
YourData
) and returns the number of differences in the two ranges. If the contents of the

two ranges are identical, the formula returns 0.

{=SUM(IF(MyData=YourData,0,1))}

The two ranges must be the same size and of the same dimensions.

This formula works by creating a new array of the same size as the ranges being compared. The

IF function fills this new array with 0s and 1s (0 if a difference is found, and 1 if the corresponding

cells are the same). The SUM function then returns the sum of the values in the array.

The following array formula, which is simpler, is another way of calculating the same result:

{=SUM(1*(MyData<>YourData))}