How to Use CHOOSE Function in Google Sheets [Purpose and Examples]

0
77
How to Use CHOOSE Function in Google Sheets

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:

CHOOSE function example in Google Sheets 1

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:

Sample data to compare choose with switch in google Sheets

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

compare choose with switch in google Sheets

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.

drop-down for Google Sheets Choose

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.

Choose Function as drop-down

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.

Match Function - Choose Function Combo in Google Sheets

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: TRUE

Formula=(A1>49)*1
Result: 1

Again;

Formula=B1>49
Result: TRUE

Formula=(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.

reverse lookup in Google Sheets using CHOOSE and MATCH 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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here