SharePoint: Choose Function

“Choose function” will return the value for a specific index. For example, if you have a set of values like “banana,” “orange,” and “apple,” the value for 3 is “apple.” The values can be dynamic, so you can define that the choices are columns instead of pre-defined values.

Think of it as an array with a search attached. It’s simple, but it can be tricky, so let’s explore it a little bit more.

Usage

It follows a simple pattern.

1. Index
2. Set of values

Example:

CHOOSE(1,[VAT Lisbon],[VAT Madeira])
The result will be
23

Or

CHOOSE(1,“banana”, “orange", “apple”)
The result will be 
"banana"

The function itself has limited functionality since you have to define everything before, so it becomes quite static. I have difficulty figuring out when to use it since, in the case above, if we wanted the value of “VAT Lisbon,” we could use the column itself in the formula instead of having the extra complexity of using this function.

Maybe someone else has examples where this may be useful, but I’m still not using it.

Limitations

There are some limitations that you should be aware:

1. You can only use 29 options.
2. Indexes start at one and not zero. The formula will not fault but it will return #Value! . Check the ISERROR function reference for how to convert these values. The same if the index is bigger than the number of items.

Recommendations:

1. Please ensure that all options are of the same data type to avoid issues. For example, if option 1 is the column “Age,” and option 2 is “Address,” then the formula will return two different data types.
2. Always define the datatype returned in the formula. If the formula returns, #Value!you’re alerted right away that something is incorrect. Also, confirm if the values returned are what you expect. It’s especially crucial if you’re returning dates since different formats can be correct in some cultures and incorrect in others.
3. The values for the options can be numbers, column references, formulas, functions, or text, so take the above recommendation seriously if you’re using functions as options.

So if you have a usage for this function, please send me an email or comment below.

Sources:

Microsoft’s CHOOSE Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by Anthony Martino on Unsplash