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