How to use the CHOOSE Function in Google Sheets

Reading Time: 4 minutes

What it does – Returns a value from a list based on a given index.

Syntax
=CHOOSE(index, choice1, [choice2, ...])

index – The choice to return.

choice1 – A potential value to return. The value can be a reference to a cell or an individual value.

choice2, ... – [Optional] Additional values among which to choose.

Sample Usage
=CHOOSE(3,"Pasta","Pizza","Burger","Biryani")

//Returns Burger as the answer, since the index is 3.

Note: The index cannot be zero, negative, or greater than the number of choices provided; the #NUM! Error is returned if is. There can be up to 29 choices.

What is the CHOOSE Function in Google Sheets

The CHOOSE function in Google Sheets returns a value from a list based on a given index. For example, =CHOOSE(2,”India”,”China”,”Russia”,”Cambodia”) returns “China”, since China is the 2nd value listed after the index number. The values passed to the CHOOSE Function in Google Sheets can also include references to cells instead of values. 

Why use the CHOOSE Function in Google Sheets

Let’s say you want to reward the employee with the highest sales with a bonus. You run a query on the sales data, and the query returns an integer, which is unique for every employee. You can now use the output of the query as an input of the CHOOSE Function in Google Sheets. If the output is 1, then the first employee listed after the index number should be awarded a bonus, if the output is 2, then the second employee and so on. You can use the CHOOSE Function along with other functions to choose from a list based on a given index.

Here, you can use the CHOOSE Function along with other functions to choose from a range of lists. The output of the dice function is fed as input to the CHOOSE Function in Google Sheets, and then the CHOOSE Function helps you decide what you will have for dinner.

Syntax

=CHOOSE(index, choice1, [choice2, …])

• Index – Which choice to return. Suppose the index is zero, negative, or greater than the number of choices provided, the #NUM! Error is returned. You can provide up to 29 choices. Also, you can use an output of another function here, given that the output is an integer between 1 and 29.
• choice1 – A potential value to return [Required]. Here, the value can be a reference to a cell, or it can be an individual value.
• choice2, … – Additional values among which to choose.

Sample Examples

• =CHOOSE(3,”Pasta”,”Pizza”,”Maggi”,”Burger”): Here, the output is Maggi as the input index is 3, and Maggi is the 3rd value listed after the index.
• =CHOOSE(A2,”Cycle”,”Car”,”Bus”,”Train”,”Flight”): Here, the output depends on the value of A2. This is an example of a reference as an index.

Make a Copy of the Spreadsheet

How to use the CHOOSE formula in Google Sheets

Example 1: Using an integer input as the index

• Let’s say you want to give an integer input to the choose function.
• Use the formula:

=CHOOSE(3,”Pasta”,”Pizza”,”Burger”,”Biryani”)

• Here, since the input index is 3, the output is Burger.

Example 2: Using the output of a function as input index

• Let’s say you want to determine the day of the week corresponding to the input date.
• Use the formula:

=CHOOSE(WEEKDAY(DATE(2022,5,28),2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")

• Here, The Date function converts the input to a date.
• Then, the Weekday function returns an integer number for the date specified.
• Since we have mentioned the second parameter as 2, days are counted from Monday, and the value of Monday is 1; therefore, the value of Sunday is 7.
• Now, the Choose Function will select the String corresponding to the number output of the WEEKDAY function.
• So let’s say the Weekday function returns a number 5, and the CHOOSE Function will choose the 5th string, i.e. Thursday.

Example 3: #NUM Error in CHOOSE Function in Google Sheets

• You will get a #NUM Error in the CHOOSE Function in Google Sheets if you:
  • Enter an index more than choices. Let’s say you use:
    • =CHOOSE(5,”Pasta”,”Pizza”,”Burger”,”Biryani”). Here, since there are only 4 choices, but you keep the index as 5, the CHOOSE Function is not able to find the 5th choice and hence, returns a #NUM Error.
  • Enter a negative index value, i.e. -2, -4 etc.
  • Enter 0 as the index value.
  • Enter index outside the range (1-29).

Conclusion

The CHOOSE Function in Google Sheets allows you to select values from a list. You can combine the CHOOSE Function with many other functions. You can go through the official documentation by clicking here.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

How to Create a Burndown Chart in Google Sheets: Learn how to create burndown chart in Google Sheets

How to create a Funnel Chart in Google Sheets: Learn how to create funnel charts in Google Sheets.

How to Use the SORTN Function in Google Sheets: Learn how to use the SORTN function in Google Sheets.

Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.