MVP #66: How To Pick Items From A List Using CHOOSE

CHOOSE is one of the formulas dedicated to making your analysis life easier. You can easily do dynamic reports just by using CHOOSE.

I have an illustration we can all easily understand. It is Sam's todo list for the day. A list of 10 tasks he has arranged in the order he intends to carry them out today.

In reality, you would want to use CHOOSE for something more complex. But the concept is the same and once you grasp how to use it here, you can use it anywhere -- even for a list of 100,000 entries in a different Excel file.

Back to our illustration.

Sam wants a simple way to see the progress he is making. He wants to type in a task number and see what it is about and if he has done it. And this where CHOOSE comes in very handy.

The way CHOOSE works is you specify the position of an item you want to pick and then list our all the items. See below how Sam has used it to solve is problem.

CHOOSE checks for what number is in cell B3 and looks through the orderly list of Description field items, then returns the one that is in the position specified in cell B3. The one above should return "Call Customer XYZ" as that is what is in the position 1 in the Description field items.

And below is a similar formula for the Remark.

Let's specify Task number 4.

Let's specify Task number 8.

Another interesting use of CHOOSE is in a Financial Model to select different projections scenarios.


For an in-depth business relevant training on Excel and Business Data Analysis, you can see the details of our next special training session here.