Our latest release supports the new formula function CHOOSE, which is an attractive alternative to the venerable IF function if you need to select a value based on an integer. The CHOOSE function is also supported by all popular spreadsheets.

## The problem with the IF formula function

One of the most popular formula function offered by spreadsheets is the IF
function. It enables you to return different values depending on the value of a
logical value, called the *condition* and given as the first parameter. The
second parameter is chosen if the condition is TRUE and the third parameter is
chosen if the condition is FALSE. To test multiple values, the third parameter
is typically set to another IF function, whose third parameter can be yet
another IF function, and so on.

Here’s a formula demonstrating nested IF functions, which returns “one” if
*Field1* is equal to 1, “two” if it is equal to 2 and “three” if it is equal
to 3:

(The third IF function has no third parameter, meaning that it returns a blank
value if *Field1* is not equal to 1, 2 or 3.)

A formula selecting a price using the IF formula function might look like this:

As you can imagine, writing such formulas quickly becomes tedious and error-prone. As all parentheses need to be closed, you’ll wind up with a large number of closing parentheses at the end of your formulas. Maintaining these formulas when you need to update your data is hard, monotonous work.

## The new CHOOSE function

Let’s say you need to select a value based on an integer ranging from 1 to an arbitrarily large number. Also, let’s say that there are no gaps, meaning that you’re interested in all integers between 1 and the arbitrarily large number. In that context, the new CHOOSE function is a very attractive alternative to IF.

The first parameter to CHOOSE is a number which tells it which parameter to choose. If the first parameter is 1, the second parameter is chosen. If the first parameter is 2, the third parameter is chosen, etc.

The IF formula above returning “one”, “two” or “three” can be written using the CHOOSE formula function as follows:

The IF function remains far more flexible than the CHOOSE function, because it can use arbitrary logical conditions and CHOOSE is only able to select a parameter based on the number you supply as the first parameter. It turns out, though, that doing so is very common.

## Selecting a value for a drop-down field with CHOOSE

What if you want to select a value based on what the user selects in a drop-down field with text values? Wouldn’t that require the use of the IF function?

Not anymore. Our new release introduces a new property for drop-down
fields, *Index*, which returns the position of the value
selected by your user. If your user selects the first value from the drop-down
field *MyDropDownField*, the formula `MyDropDownField.Index`

returns 1, and if
the drop-down field has 20 values and the user selects the last value, that same
formula returns 20.

You can use the *Index* property in conjunction with CHOOSE to quickly select
data based on the value selected by your user. Here, again, is the IF-based
formula selecting a price:

Here is the same formula, now using the CHOOSE formula function:

This version is clearly much shorter and far easier to maintain when you need to update your data. The only downside is that your formulas are now dependent on the order of your drop-down field values. If you change the ordering, you must update all formulas.

Another advantage to CHOOSE over IF is that CHOOSE should be faster, especially if you have hundreds or even thousands of values. IF needs to compare the value of the drop-down field to all the values you include in your comparisons one-by-one, whereas the CHOOSE function jumps straight to the correct value and returns it.

## How many parameters can I pass to CHOOSE?

Spreadsheets typically limit the number of parameters you can pass to so-called
*variadic functions* (which take an arbitrary number of parameters). Google
Sheets supports the CHOOSE function but limits you to 29 parameters. Microsoft
Excel is more generous and allows up to 254 parameters and LibreOffice allows
for 30 parameters.

Calcapp actually enforces no limit. Your web browser probably does have a limit, though, but it’s very high — around 30,000 parameters should work at a minimum. We don’t suggest you try to fit that much data into your formulas, though.

We added the CHOOSE function partly to let you more easily convert spreadsheet data tables to formulas. You can read more about doing the conversion here and also watch a video of the process.