Using CHOOSE() with Range References in Excel Formulas

 Last reviewed: October 31, 1994
Article ID: Q38750
The information in this article applies to:
  • Microsoft Excel for Windows, version 2,x, 3.0, 4.0, 4.0a

Summary:

Microsoft Excel returns incorrect results when you use ranges for the value argument with CHOOSE(), as in the following example: 

   =CHOOSE(1,A1:A4,B1:B4,C1:C4)
Using ranges causes Microsoft Excel to return either #VALUE or a single value that can vary depending on the location of the equation. To obtain the correct results, do one of the following:

  1. Enter the formula as an array formula using CTRL+SHIFT+ENTER for CHOOSE() to return the correct results with ranges.

  2. Enter the equation as an array formula in a single cell for Microsoft Excel to return the first value of the range based on the index_number.

When you highlight a range and enter the equation as an array formula, Excel returns values from the selected range to fill the cells or #N/A if the highlighted area is larger than the range specified in CHOOSE().

KBCategory: kbother
KBSubcategory:

Additional reference words: 2.0 2.00 2.1 2.10 3.0 3.00 4.0 4.00
4.0a 4.00a

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: October 31, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.