The CHOOSE function can return a value from a list of up to 254 arguments. It can also be a good alternative to a nested IF statement
Learn How To Use CHOOSE
In under 2 minutes, the text below will give an overview of the CHOOSE function and provide walkthroughs showing how you can evolve it to become more flexible.
=CHOOSE(index_num, value1, value2, value 3, etc…)
Let’s break that down:
=CHOOSE(index_num, This is the position in the value section where the value you want to return is held
Value1, The first value argument
Value2, The second value argument
=CHOOSE(1, “Milk”, “Cookies”) would return Milk, as this is the 1st argument in the ‘value’ portion of the statement.
But that isn’t very flexible
Correct, but we can make it much more flexible.
The above example requires you to manually enter the index_num portion of the statement. But what if your index_num isn’t static? Simple, link this to the cell that holds the correct value e.g
As long as the value in cell A1 is either 1 or 2 the formula will return either Milk or Cookies. Anything else will result in #N/A. You can use IFERROR to alert you to any problems.
But what if the answers need to change?
Another simple solution, link the ‘value’ portion of the statement to a cell with the answers in. By doing this you are able to have the formula update instantly whenever the index_num or value portions of the formula change
This formula is very easy to read, and super quick to create. If you were to create the same example using a nested if statement it would look like:
Using CHOOSE is a much faster and easier formula to type out and gives the same answer.