Excel: Comparing Single Values to Arrays

ID: Q63948

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

SUMMARY

In Microsoft Excel, a single value can be easily compared to an array, because Microsoft Excel converts a single value to an array of dimensions matching the array. However, there are some considerations. For example, the following formula can be entered as a single-value formula because it's comparing a value to an array of values:


   =OR(Rate={0.10,0.105,0.11})  TRUE if Rate equals 10%, 10.5%, or 11%

Where "Rate" is the value or cell reference to be compared to the array.

However, the following formula must be entered as an array formula (by pressing COMMAND+ENTER) because it's comparing a value to a range of cells containing values:


   {=OR(State=Z1:Z50)}       TRUE if state abbreviation found in list

In both cases, the OR() function is used to return TRUE if at least one of the array comparisons is a match. Other functions, such as AND() and SUM(), can be used to evaluate an array into a single result.

MORE INFORMATION

To see the results of each individual array comparison, enter the formula as an array in as many cells as there are comparisons. For example, the following formula


   {=Rate=E1:E3)}

can be entered as an array (press COMMAND+ENTER) in three vertical cells. Therefore, if cells E1, E2, and E3 contain 10%, 10.5%, and 11%, respectively, and Rate equals 10%, the three cells the formula was entered in will return TRUE;FALSE;FALSE, respectively.

To see how Microsoft Excel evaluates data types, select the appropriate formula text in the formula bar and choose Calculate Now from the Options menu.

By using the STEP() function, you can also see how Microsoft Excel evaluates data types in a macro.

For example, to verify that the worksheet "Sales" is currently open, the text document name can be compared to the array value returned by the DOCUMENTS() macro function:


   =OR("Sales"=DOCUMENTS())

By adding the STEP() function immediately before the above formula and holding down the SHIFT key and clicking Step in the Step dialog box when the macro is run, you can see how Microsoft Excel evaluates the formula in the following steps:


   OR("Sales"=DOCUMENTS())
   OR("Sales"={"Chart1","Macro1","Sales"})
   OR({"Sales","Sales","Sales"}={"Chart1","Macro1","Sales"})
   OR({FALSE,FALSE,TRUE})
   TRUE

Additional query words: 2.20 3.0 4.00


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999