Google Sheets Choose Function | How to Select an Element from a List | Google Sheets Function
Use Google Sheets CHOOSE function to retrieve a value from a list, based on the given index or position of the value. For example, say column A contains numbers 1, 2, 3, 4, and 5.
Column B contains 5%, 5.5%, 6%, 4.7%, and 4.5%, corresponding to the numbers in column A.
The CHOOSE function helps you retrieve the value in column B based on its index in column A.
We will take a look at the format of the CHOOSE function in Google Sheets and also look into a specific example:
=CHOOSE(index, choice1, [choice2],...)
The equal-to symbol indicates a formula follows.
CHOOSE is the name of the function.
index is a number or cell address, whose corresponding value you want to
retrieve. Example: If index is 5, then the CHOOSE function returns the fifth choice in the list.
choice1 is the first element of the list
choice2 is the second and optional element
Here is an example:
A B
--------
1 5%
2 5.5%
3 6%
4 4.7%
5 4.5%
Say you want to retrieve the value corresponding to the index 3. The formula is:
=CHOOSE(A3, B1, B2, B3, B4, B5)
The Google Sheets CHOOSE function returns 6%.
It is not necessary to use the fourth and fifth choices. You should use them, however, if you want to retrieve one of those values.
An alternative formula is:
=CHOOSE(3, 5%, 5.5%, 6%, 4.7%, 4.5)
The above formula has hard-coded index and values. It would be better to use cell address for the index and values, because if you change either the index, values or both, the the value returned by the CHOOSE function will automatically get updated.
If you want to retrieve a text element, enclose it between quotes. Here's an example:
=CHOOSE(1,"apple", "mango", "orange")
The CHOOSE function in Google Sheets returns apple.
Take a look a this video tutorial, which also has an example, on the CHOOSE function.