# CHOOSE carefully with this Excel function

Among the many functions for finding data in a spreadsheet, CHOOSE is a viable option but not always the best choice.Accountants building financial models can select from several functions to track down data in a spreadsheet. In recent months we devoted three columns to examining the following choices: **VLOOKUP and HLOOKUP**, the combination of **INDEX and MATCH**, and **LOOKUP**. This month’s article looks at another similar function, one you can **CHOOSE** but often may not want to.

**CHOOSE** is a function used in modelling but, perhaps, not as regularly as some others. **CHOOSE** is useful for noncontiguous references, as illustrated in the screenshot below.

Just so that we are clear on jargon: A **noncontiguous** range (with reference to Excel) means a range that cannot be highlighted with the mouse alone. In the image above, to highlight the cells coloured, you would have to press the **Ctrl** key as well.

**INDEX**, **LOOKUP**, **VLOOKUP**, and **HLOOKUP** all require contiguous references. They refer to lists, row vectors, column vectors, and/or arrays. **CHOOSE** is different: **=CHOOSE(index_number, value1, [value2]…)**.

**CHOOSE** allows references to different calculations, workbook/worksheet references, etc. This versatility can make **CHOOSE** an enticing option, but I would advise using discretion when determining if **CHOOSE** is the best function to use. To understand why, you need to have a grasp on how **CHOOSE** works.

**CHOOSE** uses **index_number** to return a value from the list of value arguments. Modellers can use **CHOOSE** to select one of up to 254 values based on the index number (**index_number**). For example, if **value1** through **value7** are replaced with the days of the week:

**=CHOOSE(index_number, “Sunday”, “Monday”, “Tuesday”, “Wednesday”, Thursday”, “Friday”, “Saturday”)**

this formula returns one of the days when a number between 1 and 7 is used as **index_number**.

The **CHOOSE **function employs the following syntax to operate:

**CHOOSE(index_number, value1, [value2])**

The function has the following arguments:

**index_number:**This is required and used to specify which value argument is to be selected. The argument**index_number**must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254:- If
**index_number**is 1,**CHOOSE**returns**value1**; if it is 2,**CHOOSE**returns**value2**; and so on. - If
**index_number**is less than 1 or greater than the number of the last value in the list,**CHOOSE**returns the**#VALUE!**error value. - If
**index_number**is a fraction, it is truncated to the lowest integer before being used.

- If
**value1**,**value2**,**...**:**value 1**is required, but subsequent values are optional. There may be between 1 and 254 value arguments from which**CHOOSE**selects a value or an action to perform based on**index_number**. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

It should be further noted that:

- If
**index_number**is an array, every value is evaluated when**CHOOSE**is evaluated. - The value arguments to
**CHOOSE**can be range references as well as single values.

It is important to consider carefully when it is appropriate to use **CHOOSE**. I am pickier about using **CHOOSE** than many. For instance, a well-known Excel website proposes the following formula for calculating the US Thanksgiving date:

**=DATE(A1,11,CHOOSE(WEEKDAY(DATE(A1,11,1)),26,25,24,23,22,28,27))**

To understand this formula, assume that cell **A1 **contains the year and note that **DATE(Year,Month,Day)** returns a date and **WEEKDAY(Date)** returns a number 1 (Sunday) through 7 (Saturday). The formula works, but doesn’t it look horrible? It is full of hard code and contains an unnecessary number of arguments. The formula could exclude **CHOOSE ***and be rendered as: ***=DATE(A1,11,28-MOD(WEEKDAY(DATE(A1,11,1))+1,7))**.

Now let me be clear here. I am not saying this is a simple, transparent formula, but test it against the **CHOOSE **option. Both formulas provide the same answer. **CHOOSE** — and plenty of additional hard code — has been used unnecessarily.

That’s not to say there isn’t a time and a place for **CHOOSE**. It is useful when you need to refer to cells on different worksheets or in other workbooks. But it’s more often not the best choice.

For example, some argue that **CHOOSE** is useful when a calculation needs to be computed using different methods, eg,** =CHOOSE(index_number, calculation1, calculation2, calculation3, calculation4)**.

I disagree, and here’s why. In the example below, I have created a lookup table in cells **E10:E13** that I have called **Data **(to create a range name, simply select the cell(s) and type the name into the “name box”, which is the area to the left of the formula bar). The calculations are all visible on the worksheet, rather than hidden away in the formula bar. The **index_number **selection, here referred to as **Selection_Number**, is inputted in cell **E2**. The formula, which is clearly visible in cell **E4**, is **=INDEX(Data,Selection_Number)**. The **INDEX** formula produces the same result as the **CHOOSE** formula in the previous paragraph, but it is much more transparent and easier to follow.

I have taught financial modelling to many gifted analysts over the years, and a common mistake they make is building models that are easy to build rather than *models that are easy to understand*. The end user is the customer. The spreadsheet should be simple for the end user to use: Taking shortcuts in building the model invariably helps only the modeller — and even then, more often than not, shortcuts will backfire.

**CHOOSE** can lead to opaque models that need to be rebuilt and are often less flexible to use. So **CHOOSE** wisely.

*— Liam Bastick**, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *An Introduction to Financial Modelling*. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an *FM* magazine senior editor, at Jeff.Drew@aicpa-cima.com.*