Choose Function in Google Sheets

What Is CHOOSE Function in Google Sheets?

The CHOOSE function in Google Sheets selects a value from a list of choices based on an index number. It is a powerful function when it comes to data selection. When you have to choose an option dynamically based on specific criteria, this function comes in handy. Use the CHOOSE function in Google Sheets to enhance the readability of your data.

Let us look at a simple example. For example, if you have a list of students in cells A1:A3 in an order based on their grades in an exam.  You want to select a student who has secured the first rank based on a number in cell B1. Use the following formula in such a scenario in cell B4.

=CHOOSE(B1, A2,A3,A4)

Here, B1 contains the value 1; the formula will return the value in cell A2. If B1 contains 3, the formula will return the value in cell A4.

CHOOSE() Google Sheets Function Syntax

The syntax for CHOOSE() in Google Sheets is as follows:

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

• “index” is the number that indicates the choice to select. The value should be between 1 and 254
• “choice1”, “choice2”, etc., are the expressions, values, or references from which to choose. Up to 254 choices can be added.

How to Use CHOOSE Function in Google Sheets?

Here’s a step-by-step guide on how to use the CHOOSE function in Google Sheets.

Step 1:  Enter the required data in a Google Sheets function. We are entering the days of the week here.

Step 2: Select the cell where you want to enter the CHOOSE function. Start by typing the following in cell C2, which we have selected.

=CHOOSE( to start the function.

Step 3:  Now, we enter the index number as the first argument. Here, we wish to find the third day of the week. Next, enter the days of the week using the cell references for the choices.

=CHOOSE(3, A1,A2,A3,A4,A5,A6,A7)

Step 4:  Close the Function with a closing parenthesis ) and press Enter. You will see Tuesday as the third item on this list.

Using the Google Menubar

1. To enter the function through the Google menu, choose the cell where you want to enter the formula.
2. Now, go to the menu bar and click on “Insert” ➝ “Function” ➝ “Lookup” ➝ “CHOOSE”
3. Enter the arguments, which include the index number and the list of values as arguments. Close the bracket and press the “Enter” key.

Examples

Let us look at some examples of how to use the CHOOSE function in Google Sheets under different everyday scenarios.

Example #1 – Nested Choose Functions For Dynamic Text String In Google Sheets

We are entering a number dynamically in a Google sheet. Based on that number, we will be choosing whether to display the email ID or phone number.

Step 1: Let us assume we are entering either 1, 2, or 3 in cell A1. Based on that, we will slowly build the query.

Starting the output with text, write the following choose option in cell B2.

=”Please” &CHOOSE(A1, “Email”,”Call”,”Check Facebook page at”

In this part, we enter the first choose function. Based on the entry 1,2,3 in cell A1, we either get Email, Call, or Check Facebook Page At.

Step 2: Now, close the braces and append the second CHOOSE function.

=”Please” &CHOOSE(A1, “Email”,”Call”,”Check Facebook page at”) &CHOOSE(A1, ” xyz@google.com”, ” 9823412345″,” Beautiful Weaves”)

Here, again, based on the number in A1, we will display the email ID, phone number, or Facebook page name.

Step 3: Enter 2 in the A1 cell, press Enter, and observe the result.

You can now enter different numbers and check how the values in B2 change dynamically. Let’s enter 3 in A1.

Example #2 – Using Choose with RANDBETWEEN For Basic Random Selection

In the following example, we use the RANDBETWEEN function along with CHOOSE to display a value based on a random number. Let us suppose RANDBETWEEN chooses any number between 0 and 4; based on the choice, we will display the output.  

Look at the table below, where values will be displayed based on the temperature number chosen.

Step 1: Apply the following function to cell B3.

Step 2: Press Enter. RANDBETWEEN generates a random number between 1 and 5, and CHOOSE returns a corresponding color from the list of five colors in Column A.

It returns pink, which means RANDBETWEEN returned 3. Let us apply the function again and check the result. Click on B3, copy and paste the formula, and press Enter again.

Now, RANDBETWEEN chose 5.

Example #3 – Using the CHOOSE function for Smart Chip-based 5-star rating

Not many of us will know that we have an exciting feature in Google Sheets, which is smart chip-based rating. To find this, go to “Insert” > “Smart chips” > “Rating.”

Step 1: Let us use it along with the CHOOSE function. Let us enter some rating values entered by customers for a restaurant.

Step 2: As mentioned earlier, go to Insert ->Smart chips->Rating. You get five stars. Copy it from B1 to B5.

Step 3: Now, enter the CHOOSE in the same cells. Let us try B2. Enter the following formula.

=CHOOSE(A2,1,2,3,4,5).

As you can notice, based on the A2 value, 3, we get three stars. Copy the formula to all cells from B3 to B6.

Example #4 – Returning Month Name From Date

You can use the CHOOSE function in Google sheets to return the month. The MONTH function returns the month number when you use it against a date. For instance, it returns 1 for January, 2 for February, and so on. Let us find the month of today’s date and use the choose function to display the month name.

Step 1: To find the month of today’s date, we should use the formula =MONTH(TODAY()). Use this as the first argument of the CHOOSE function.

Step 2: The output of this acts as an index number. Now, enter January, February, etc., as the choices for the CHOOSE function in Google Sheets.

Step 3: Press Enter. You get the current month’s name.

Alternatives to CHOOSE in Google Sheets

While CHOOSE has its unique features, other functions are an alternative to it. These include IF, VLOOKUP, INDEX and MATCH functions.

• One of the commonly used functions in Google Sheets is IF, which returns a value based on specific conditions. However, we can use the CHOOSE function as a quick and simpler alternative.
• Similar to VLOOKUP, you can choose a value using CHOOSE based on the index number. However, with CHOOSE, the list is within the function, while with VLOOKUP, it helps you look up values with a reference to a list of items in a VLOOKUP column. Hence, we can use VLOOKUP with the Choose function in Google Sheets.
• CHOOSE cannot retrieve an item from a range or array constant provided as an argument. For larger data sets of data in a range, INDEX and MATCH are better alternatives to retrieve a value based on position.

Important Things to Note

• Use direct cell references in the Choose function arguments compared to expressions to avoid unnecessary issues.
• If the argument index_num is less than 1 or greater than the count of values in the list, you get the #VALUE! error.
• Choose function in Google Sheets does not require the data to be in any particular order, thereby showing its flexibility.
• If the index number in the CHOOSE function is not an integer, Excel will round it down to the nearest integer part.
• If more than 254 values are given in the CHOOSE function, a #VALUE! error will occur.

Frequently Asked Questions (FAQs)

Recommended Articles

Guide to What is CHOOSE Function in Google Sheets. Here we discuss how to use CHOOSE Function in Google Sheets with its examples. You can learn more from the following articles. –

Datedif in Google Sheets

Find and Replace in Google Sheets