####
**Best Online Course :**
SAS Programming with Practical Examples

- Explain Programming Concepts in Simple English

- Live Projects & Case Studies

- Job Placement Assistance

- Get 10% off till Jan 22, 2018

- Batch starts from February 10, 2018

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

2. If a value in cell C6 is 2, we want the

3. If a value in cell C6 is 3, we want the

4. If a value in cell C6 is 4, 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**

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

## Post a Comment