Excel CHOOSE Function and Its Uses

Deepanshu Bhalla Add Comment
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
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "Excel CHOOSE Function and Its Uses"
Next → ← Prev