Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/38750

From BetaArchive Wiki


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


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