- Using CHOOSE() with Dates
- More Power of Choice
- Going Beyond 29 Choices
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Going Beyond 29 Choices
Although the 29-item limit of the CHOOSE() function is rarely a problem, there might be times when you want to choose from more than 29 different items. You can combine CHOOSE() with the IF() function to overcome this limit. Suppose that there are 50 values to choose from, and the numeric value, ranging from 1 to 50, is in cell A1. You could write something like this:
=IF(A1>29, CHOOSE(A1-29, val30, val31,...,val50), CHOOSE(A1, val1, val2,...,val29))
Here’s how it works. If the value in cell A1 is greater than 29, the IF() function selects the first version of CHOOSE(), which subtracts 29 from the value in A1 resulting in index values ranging from 1 (if A1 is 30) to 21 (if A1 is 50). If, however, the value in cell A1 is 29 or less, the IF() function selects the second version of CHOOSE(), which uses the value in cell A1 directly. If you want to go beyond 58 value (29 times 2), things get a bit more complicated, and I’ll leave that for you to figure out if and when you need it.