In Google Sheets, the **CHOOSE Function** is not much common in use. Many compare it with other Google Sheets functions like Switch as well as Nested IF. But for me, there is no point in such a comparison. I can explain you why I say so. Now time to know how to use Choose Function in Google Sheets.

**Google Sheets CHOOSE Formula Syntax**

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

There is no point in explaining the syntax without examples. So, see that below.

**Purpose of CHOOSE Function in Google Sheets**

The Choose function returns an element from a list of choices based on index.

=CHOOSE(2,”USA”,”UK”,”INDIA”,”JAPAN”,”AUSTRALIA”)

Here 2 is the index number. So the formula would return “UK” as it is the matching element. If the index number is 1, then the Choose Formula would return “USA”.

**Why Choose is Not Similar to Switch or Nested IF**

Earlier in this tutorial, I’ve told you that Google Sheets Choose function is not similar to SWITCH or Nested IF. So I think it’s my responsibility to point out why.

Both Switch and IF are Logical Functions. But Choose is a Lookup Function. So with Choose, we can’t make logical test in a ‘widest’ form. But we can do some limited logical test using Google Sheets Choose Function also. I will explain the same below in the example section. These are the main differences.

**How to Use CHOOSE Function in Google Sheets**

I’ve some examples to the use of Google Sheets CHOOSE function. I’m going to use Choose Formula in the following different scenarios.

- To ‘remotely’ control the SUM Range.
- A simple reverse vlookup – Only Applicable to a small data set.
- How to use Choose as an alternative to SWITCH formula – Only possible with small data set.
- Finally as a replacement to IF logical funtion.

Similar: Index Match – Better Alternative to Vlookup and Hlookup in Google Sheets

**Example 1:**

How to use Choose Function in Google Sheets to Control SUM formula Range?

Sample Data:

The below CHOOSE formula can bring some dynamism to SUM function. How?

=sum(choose(

1,C2:C5,C6:C9))

This formula would SUM the range C2:C5 as it’s the choice in the Choose formula. When you change the Choose formula Index to 2, then it would SUM range C6:C9. Here you can assume Index 1 as “Student 1” and Index 2 as “Student 2”. You can also refer Index number to any cell. So from outside the formula, you can control the sum range.

**Example 2:**

Here I’m going to compare Google Sheets Choose Function with SWITCH function.

Sample Data:

The following Switch Formula as well as Choose Formula return the results as below in Column C.

**SWITCH:**

=switch(B1:B7,”Sunday”,”Weekend”,”Saturday”,”Weekend”,”Working Day”)

**CHOOSE:**

=ArrayFormula(choose(A1:A7,{“Weekend”;”Working Day”;”Working Day”;”Working Day”;”Working Day”;”Working Day”;”Weekend”}))

Here Google Sheets SWITCH formula is far better than CHOOSE Formula. Because Choose formula here requires an additional ArrayFormula and Column A.

**Example 3:**

Here is a better use of Google Sheets CHOOSE function. We can effectively use Choose in a drop-down selection.

Here I’m using the same sample data in Example 2.

First I’m creating a drop down menu in Cell F using the values in Column A.

Now I’m applying the below formula in Cell G1.

=choose(F1,D1,D2,D3,D4,D5,D6,D7)

It would be look like this.

Here the drop-down menu acts as the Choose Function Index.

**Example 4:**

How to use Choose Formula in Google Sheets with MATCH function. This is an example to MATCH – CHOOSE Combo to work similar to Vlookup.

The MATCH, which is another Google Sheets Lookup function, returns the relative position of an item in a range. This position number, we can use in Choose Function as Index as below.

This formula would return the result weekend as the match function returns the relative position number 7.

=choose(match(“Saturday”,B1:B7,0),D1,D2,D3,D4,D5,D6,D7)

**Example 5:**

Choose Formula as an alternative to IF Logical function in Google Sheets.

=if(and(A1>51,B1>51),”Passed”,”Failed”)

The above formula is to perform a logical test in Cell A1 and Cell B1. If values in both the cells are above 49, it will return “Passed” else “Failed”.

See how can I replace the above IF formula with CHOOSE formula.

=CHOOSE(((A1>49)*1+(B1>49)*1)+1, “Failed”,”Failed”, “Passed”)

As you know, the CHOOSE function can’t perform a logical test as its core purpose is LOOKUP. But we can do some trick here. In the first part of the formula, that means INDEX number, we can mimic a logical test. See how that formula part developes.

Formula=A1>49

Result: TRUEFormula=(A1>49)*1

Result: 1

Again;

Formula=B1>49

Result: TRUEFormula=(B1>49)*1

Result: 1

Now the final INDEX part would return 1+1+1, that’s 3. So you can read the above formula as;

=CHOOSE(3, “Failed”,”Failed”, “Passed”)

**Example 6:**

Finally, let’s see how to perform a reverse or backward lookup using Google Sheets CHOOSE function.

Here I’m leaving this to you to analyse as I’ve already given the tips in example 4 above. That’s all for now. Thanks for the stay!