Microsoft Excel Formulas: CHOOSE

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 Syntax

=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

 

For Example

=CHOOSE(1, “Milk”, “Cookies”) would return Milk, as this is the 1st argument in the ‘value’ portion of the statement.

CHOOSE
Click on the image to expand it
CHOOSE
Click on the image to expand it

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

=CHOOSE(A1,”Milk”,”Cookies”)

CHOOSE
Click on the image to expand it

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

=CHOOSE(A1,E1,E2,E3,E4,E5)

CHOOSE
Click on the image to expand it

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:

=IF(A1=1,E1,IF(A1=2,E2,IF(A1=3,E3,IF(A1=4,E4,IF(A1=5,E5)))))

Using CHOOSE is a much faster and easier formula to type out and gives the same answer.

 

 

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Excel Basics

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *