In this article, you will learn how to use the CHOOSE formula in Excel.
The CHOOSE formula in Excel is a function that allows you to select a value from a list of values based on a specified index number.
The CHOOSE function in Excel is helpful because it allows you to select a value from a list of values based on a specified index number, which can be helpful in various situations. Some examples of when you might use the CHOOSE function include:
The syntax of the formula is as follows:
"index_num" is the position of the value you want to select (e.g., 1 for the first value, 2 for the second value, etc.). This argument should be an integer between 1 and 254 or a cell reference containing a number within the range. If the number in this parameter is out of the range, the formula returns the #VALUE@ error value.
"value1, value2, ..." are the values from which you want to select. The “value1” is mandatory. This argument can accept numbers, dates, formulas, text, and cell references.
For example, if you have a list of values in cells B3:B5 and you want to select the value in cell B4, you would use the formula below:
Assume you want to create three scenarios on your revenue forecast for the next five years - Upside, Base, and Downside cases for your financial analysis. You can insert the CHOOSE function in cells C10, D10, E10, F10, and G10. In each formula, the “value1”, “value2”, and “value3” should refer to the numbers of “Scenario 1”, “Scenario 2”, and “Scenario 3”, respectively, in the same column. In the example below, as the “index_num” is 1, the “value1” is returned in each cell containing the CHOOSE function. The following picture shows what the CHOOSE function in cell G10 looks like.
In the following example, we enter the CHOOSE functions in cells D20, E20, F20, and G20, in which the revenue growth changes depending on the selected scenario. Revenue is calculated based on the chosen growths. As the “index_num” is 3, each of the cells containing the CHOOSE formula returns 12% (“value3”). The function included in cell G20 is shown in the formula bar.
The last example shows the CHOOSE function containing other functions, specifically, MAX, MIN, and AVERAGE formulas. In the example, the formula returns the minimum value in cells C27:C31, which is 1000, because the “index_num” is 2 and the MIN function in “value2”, which finds the smallest number in a selected range, is applied to the range (C27:C31).
Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇