The CHOOSE function allows us to choose a value from a list of values. Each value within said list will have an index that begins with the number one and will increase for each element of the list. The first argument of the function will allow us to indicate the element that we want to obtain from the entire list.
To properly understand the CHOOSE function we will do an example. Suppose a list of the days of the week: “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”. With this order of days, Sunday will be assigned index 1, Monday will be index 2, and so on.
If we provide these values to the CHOOSE function and we want to obtain Tuesday as the result, then we must create a formula like the following:
=ELEGIR(3, "Domingo", "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Sábado")
The first argument of the function will indicate the index of the value that we want to obtain and the list of possible values is indicated from the second argument of the CHOOSE function. The following image shows the result is this formula:
The CHOOSE function in action
The above formula could help us to easily display the day of the week that corresponds to a certain date. Let’s do a second example to show the utility of the CHOOSE function and to use the data shown in the following image:
The spine it simply has a list of dates and the column use the WEEKDAY function to know the day of the week that corresponds to the indicated date. Notice that the WEEKDAY function uses the value 1 in its second argument, which will ask the function to consider a week that starts on Sunday.
In this way, the date 01/23/2015 in cell A2 corresponds to a Friday, the date 02/02/2015 is a Monday, and so on. However, we do not want to have the numbers but the names of the days and for that we can use the same formula from the previous exercise.
Although there are other ways to display the name of the day of a date, I have used this example to show a practical use of the CHOOSE function. And what I want to highlight is that the first argument of the function must always be an integer because it refers to the index () of the elements indicated in the function.
The trick that I want to show you this time is that we can use the CHOOSE function to make a decision about the value of the list to display even when the value that determines the index is based on a letter.
A different example with the CHOOSE function
The following figure shows data on the grades of a group of students. It is not a numerical grade, but rather a letter scale has been used.
We want to create an additional column, which helps us to display a word based on the letter of the column . The equivalence we want to use is the following:
A = Excellent
B = Good
C = Acceptable
D = Low
F = Failed
There are several methods that we can use to accomplish this task. We could use the VLOOKUP function to find the corresponding word for each letter and we could also use the nested IF function to compare each letter and return the appropriate word.
However, we are going to use the CHOOSE function to see the potential of that function and we will do a little trick to return the correct word based on the indicated character. We will achieve this by using the CODE function.
The CODE function in Excel
The CODE function returns the code number of the indicated character. Since the column of our data is a character, we can obtain its numerical value in the following way:
CODE (“A”) = 65
CODE (“B”) = 66
CODE (“C”) = 67
CODE (“D”) = 68
CODE (“F”) = 70
Since the CODE function obtains the numerical equivalent of a letter, we can use it to help us generate the numerical value that we should use as an index in our CHOOSE function.
Something very important to note is that the numerical value of a capital letter is different than for a lowercase letter, so in our example we will make sure to use all the letters in the column as uppercase.
The CHOOSE function with characters as index
The formula that the CHOOSE function uses to display the equivalent word based on the column letter is the next:
=ELEGIR(CODIGO(B2) - 64,"Excelente","Bueno","Aceptable","Bajo","","Reprobado")
Notice two important things in this formula. First, the first argument of the CHOOSE function uses the CODE function to obtain the numeric value of the letter indicated in the column but it subtracts the value 64 because we need the value of the letter “A” to correspond to the number 1. In this way, the letter “A” will return the first word in the list of values, which is the word “Excellent” and so on .
Second, note that the penultimate of the listed values is an empty text string because it is the value that would correspond to the letter “E”, and although said letter is not present in the list of qualifications, when indicating a value in position five in the list will help us to get the next word, which is “Failed”, to get index six and therefore be returned properly when the letter “F” is found.
The following image shows the result of the above formula on the sample data:
An improvement to this example is that the CHOOSE function takes the values from the list using a reference to another cell as follows:
=ELEGIR(CODIGO(B2) - 64, $E$2, $E$3, $E$4, $E$5, "", $E$6)
The benefit of this formula is that we could change the words at the moment we need it without having to modify the formula. In this case it is necessary to use absolute references so that they are not modified when copying the formula to all cells. Look at the result of this formula:
Download the workbook that I used in the making of this article and experiment for yourself with the CHOOSE function.