Excel CHOOSE Function and Its Uses

Explanation :
The CHOOSE function commands excel to activate a value or formula in a list of values or formulas.

Syntax :
= CHOOSE (Index number, value1 , value2 .........up to 254 values)

Download the workbook used in the examples shown above. 

Examples :

1.  Level : Easy

Condition 1 : If a value in cell B6 is 1, we want the function to place "Apple" in cell C6.
Condition 2 : If a value in cell B6 is 2, we want the function to place "Orange" in cell C6.
Condition 3 : If a value in cell B6 is 3, we want the function to place "Mango" in cell C6.


In cell C6, enter the following formula - 
= CHOOSE (B6, "Apple", "Orange" , "Mango")
In simple words, the above formula means :
= CHOOSE ( Extract kth value from the list - {Apple,Oranges,Mangoes,Bananas})
If B6 = 1 , it returns Apple as it is the first value in list.
If B6 = 2 , it returns Orange as it is the second value in list.
If B6 = 3 , it returns Mango as it is the third value in list.


2.  Level : Medium
 Example 1
Activate function based on chosen drop down value





=CHOOSE(C6, MIN(F5:F10)MAX(F5:F10), SUM(F5:F10)AVERAGE(F5:F10))
In simple words, the above formula means :
1. If a value in cell C6 is 1, we want the CHOOSE function to calculate minimum value
2. If a value in cell C6 is 2, we want the CHOOSE function to calculate maximum value
3. If a value in cell C6 is 3, we want the CHOOSE function to add all the numbers
4. If a value in cell C6 is 4, we want the CHOOSE function to average all the numbers


 Example 2
Reverse VLOOKUP - VLOOKUP to the left
VLOOKUP looks for a value in the leftmost column of a table, and then returns a value to the right of that column. If one wants to look up a value in one column and return values to the left of that column.



=VLOOKUP(E18,CHOOSE({1,2},C18:C23,B18:B23),2,0)

In simple words, the above formula means : { C18:C23 is 1 , B18:B23 is 2 }
=VLOOKUP(Lookup value, Extract a value from the second range)


3.  Level : Difficult

Alternative to Nested IF Function - CHOOSE


=CHOOSE((cell_reference>0) + (cell_reference>40) + (cell_reference>60) + (cell_reference>80)+(cell_reference>90),"Poor","Average","Fair","Good","Excellent")

For example, 75 is in cell L18. We want the function to return grade against it in cell M18.
=CHOOSE((L18>0) + (L18>40) + (L18>60) + (L18>80) + (L18>90), "Poor","Average","Fair",
"Good", "Excellent")

Result : Fair

How it works :
=CHOOSE( (75>0) + (75>40) + (75>60) + (75>80) + (75>90), "Poor",,,,,,,,)
=CHOOSE( 1 + 1 + 1 + 0 + 0 , "Poor","Average","Fair","Good", "Excellent")
=CHOOSE( 3, "Poor","Average","Fair","Good", "Excellent")

Download this workbook

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "Excel CHOOSE Function and Its Uses"

Post a Comment

Next → ← Prev