Microsoft KB Archive/38750

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: Enter the formula as an array formula using CTRL+SHIFT+ENTER for CHOOSE() to return the correct results with ranges. 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.