The CHOOSE formula in Excel is one of those, which is quite unknown. It can be very helpful though: For example it can easily convert the weekday-number into the weekday name. Often, it is used within other formulas. In this article you learn everything you need to know about the formula.
Structure of the CHOOSE formula
The CHOOSE formula returns a value from a list of values. So basically you provide a list of values (or references) and tell Excel, which one of these values to return.
The structure is quite straightforward (see the image above):
- The INDEX number determines, which item from the following list the formula has to return.
- You have at least to provide one option as the return value. This could be a fixed value or another cell reference.
- The CHOOSE formula would make much sense if you don’t provide more than one value to choose from. So number 3 in Figure 44 stands for the second value to choose from.
- You can add up to 254 values.
Example for the CHOOSE formula
The first example is very simple. You got the two data sets as shown in Figure 45. “BMW” costs 15,000 USD and Mercedes 20,000 USD.
In cell E2 you select which value to return, either the first one (price of BMW, 15,000 USD) or the second one (the price of Mercedes, 20,000 USD).
The first argument of the CHOOSE formula selects which value to return. In this case, it refers to cell E2 which says 2. The second part (“B3”) and the third part (“B4”) contain the list to choose from.
So, what will the formula in E3 return after pressing Enter on the keyboard? Correct, it’s 20,000. Why? Because the number 2 in cell E3 determines, that the second item from the list in the CHOOSE formula will be returned. The second item of the list is the cell reference to cell B4 so that the value 20,000 from cell B4 is returned.
Another example for the CHOOSE formula
After you’ve explored a simple example, you can now take a look at a more advanced one as pictured in Figure 46.
In cell F2 you want to type a year, for example 2017. In the cell below you want to get the sum of the prices of the selected year of the two cars shown on the left-hand side. So if you type 2017 in F2, you want to get the sum of 16,000 USD and 20,500 USD from cells C3 to C4.
That means, you basically need the SUM formula for adding up the values. But instead of providing a fixed range, the cell reference should switch between the two columns B and C.
The first part of the CHOOSE formula determines, which of the following cell references to use. You could simple insert the difference of 2018 and the value in F2. So if you write 2017 into cell F2, 2018 – 2016 is 1 so that CHOOSE returns the first item from the cell reference list. If you type 2016 on the other hand, the formula returns the second cell reference as 2018-2016=2. So you only have to give the correct cell references as the second and third part of the CHOOSE formula. The complete formula is:
=SUM(CHOOSE(2018-F2,C3:C4,B3:B4))A popular example: The weekday name
The CHOOSE formula is probably most often used in context with the weekday name. You want to get the name of the weekday from a cell containing a date.
If you want to get the weekday name of the date, given in another cell, please copy and paste this formula. Instead of “A1” within the WEEKDAY formula, just refer to the cell you like.
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
Please refer to this article for more information about the weekday name.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
