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.
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.
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
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",
=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
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
Share Share Tweet