Excel: Comparing Single Values to Arrays

 Last reviewed: November 29, 1994
Article 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

KBCategory: kbusage
KBSubcategory:

Additional referenceS

"Microsoft Excel User's Guide 2," version 4.0, pages 153-166
"Microsoft Excel User's Guide," version 3.0, pages 268-281
"Microsoft Excel Function Reference," version 4.0, pages 118-119 and
310-311
"Microsoft Excel Function Reference," version 3.0, pages 60-61 and 172

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.2 2.20 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.