✨ **Part 1. Introduce – CHOOSE function in Microsoft Excel:** ✨

**Definition:** The CHOOSE function in Microsoft Excel is a powerful lookup function that allows you to return a value from a list of values based on a specified index number. It is beneficial when you have a list of possible values and want to select one based on a numeric index.

**Purpose:** The purpose of the CHOOSE function is to simplify the process of selecting a value from a list based on its position. It is commonly used to avoid nested IF statements and make the formula more readable.

**Syntax & Arguments:** The syntax of the CHOOSE function is as follows:

`CHOOSE(index_num, value1, value2, value3, ...) `

`index_num`

: The index number that specifies which value to return. It must be an integer between 1 and the total values provided.`value1, value2, value3, ...`

: The importance of choosing from. You can provide up to 254 deals.

**Explain the Arguments in the function:**

`index_num`

: This is the position of the value you want to return from the list. For example, if`index_num`

it is 2, the function will return the second value from the list.`value1, value2, value3, ...`

: These are the values from which you want to select based on the`index_num`

. You can provide multiple values separated by commas.

**Return value:** The CHOOSE function returns the value from the specified position in the list of values based on the given `index_num`

.

**Remarks:**

- The
`index_num`

must be an integer. Excel will round it down to the nearest integer if it is a decimal or non-numeric value. - If the
`index_num`

is less than 1 or greater than the number of values provided, the function will return a #VALUE! Error. - CHOOSE function can be helpful when creating custom messages or retrieving specific values from a list based on certain conditions.

✨ **Part 2. Examples of CHOOSE Function:** ✨

**Example 1: Selecting a Color for Sales Performance**

A | B | C | |
---|---|---|---|

1 | Sales | Performance | Color |

2 | $2000 | Good | `=CHOOSE(IF(B2="Excellent", 3, IF(B2="Good", 2, IF(B2="Satisfactory", 1, 0))), "Red", "Yellow", "Green")` |

3 | $3500 | Excellent | `=CHOOSE(IF(B3="Excellent", 3, IF(B3="Good", 2, IF(B3="Satisfactory", 1, 0))), "Red", "Yellow", "Green")` |

4 | $1200 | Satisfactory | `=CHOOSE(IF(B4="Excellent", 3, IF(B4="Good", 2, IF(B4="Satisfactory", 1, 0))), "Red", "Yellow", "Green")` |

Explanation:

- We have a table with sales performance and want to assign colors based on the performance.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the performance level.
- If the performance is “Excellent,” it returns 3 (corresponding to “Red”), “Good” returns 2 (related to “Yellow”), and “Satisfactory” returns 1 (connected to “Green”).

**Example 2: Employee Bonus Calculation**

A | B | C | D | |
---|---|---|---|---|

1 | Employee | Sales | Target Sales | Bonus Amount |

2 | John | $12000 | $10000 | `=CHOOSE(IF(B2>=C2*1.2, 3, IF(B2>=C2, 2, IF(B2>=C2*0.9, 1, 0))), 300, 200, 100, 0)` |

3 | Alice | $9000 | $8000 | `=CHOOSE(IF(B3>=C3*1.2, 3, IF(B3>=C3, 2, IF(B3>=C3*0.9, 1, 0))), 300, 200, 100, 0)` |

4 | Bob | $8000 | $9000 | `=CHOOSE(IF(B4>=C4*1.2, 3, IF(B4>=C4, 2, IF(B4>=C4*0.9, 1, 0))), 300, 200, 100, 0)` |

Explanation:

- We have a table with employee sales data and target sales.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the sales performance relative to the target.
- If the sales exceed 120% of the target, it returns 3 (corresponding to a bonus of $300); if sales are equal to the target, it returns 2 (corresponding to a prize of $200). And if sales are at least 90% of the target, it returns 1 (corresponding to a bonus of $100). Otherwise, it returns 0 (no compensation).

**Example 3: Product Pricing Tier**

A | B | C | |
---|---|---|---|

1 | Product | Price | Price Tier |

2 | Product A | $15 | `=CHOOSE(IF(B2>=20, 3, IF(B2>=10, 2, 1)), "High", "Medium", "Low")` |

3 | Product B | $8 | `=CHOOSE(IF(B3>=20, 3, IF(B3>=10, 2, 1)), "High", "Medium", "Low")` |

4 | Product C | $25 | `=CHOOSE(IF(B4>=20, 3, IF(B4>=10, 2, 1)), "High", "Medium", "Low")` |

Explanation:

- We have a table with product prices and want to categorize them into different price tiers.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the price value.
- If the price is $20 or more, it returns 3 (corresponding to the “High” tier). If it is at least $10, it returns 2 (related to the “Medium” tier), and if it is less than $10, it returns 1 (corresponding to the “Low” level).

✨ **Example 4: Selecting Shipping Method**

Suppose you have a table with different shipping options and their corresponding costs, and you want to select the most cost-effective shipping method based on the total order amount:

A | B | C | D | |
---|---|---|---|---|

1 | Order Total | Method | Shipping Cost | Selected Method |

2 | $200 | Standard | $10 | `=CHOOSE(IF(A2>=100, 2, 1), B2, "Express")` |

3 | $120 | Express | $15 | `=CHOOSE(IF(A3>=100, 2, 1), B3, "Standard")` |

4 | $80 | Standard | $8 | `=CHOOSE(IF(A4>=100, 2, 1), B4, "Express")` |

Explanation:

- We have a table with order totals and the available shipping methods with their costs.
- The CHOOSE function is nested with an IF function to determine the index_num based on the order total.
- If the order total is $100 or more, it returns 2 (corresponding to the “Express” method). Otherwise, it returns 1 (related to the “Standard” method).

✨ **Example 5: Categorizing Temperature**

Let’s categorize temperatures into different weather conditions based on the given range:

A | B | C | |
---|---|---|---|

1 | Location | Temp | Weather |

2 | City A | 30°C | `=CHOOSE(IF(B2>30, 3, IF(B2>20, 2, 1)), "Hot", "Warm", "Mild")` |

3 | City B | 18°C | `=CHOOSE(IF(B3>30, 3, IF(B3>20, 2, 1)), "Hot", "Warm", "Mild")` |

4 | City C | 25°C | `=CHOOSE(IF(B4>30, 3, IF(B4>20, 2, 1)), "Hot", "Warm", "Mild")` |

Explanation:

- We have a table with different city temperatures and want to categorize them into other weather conditions.
- The CHOOSE function is nested with an IF function to determine the index_num based on the temperature range.
- If the temperature exceeds 30°C, it returns 3 (corresponding to “Hot”). If it is above 20°C, it returns 2 (corresponding to “Warm”). Otherwise, it returns 1 (corresponding to “Mild”).

✨ **Example 6: Selecting Payment Method**

Let’s choose a payment method based on the customer’s location and their preferred payment option:

A | B | C | D | |
---|---|---|---|---|

1 | Customer | Location | Payment | Selected Payment |

2 | John | US | Credit Card | `=CHOOSE(IF(B2="US", 2, 1), C2, "PayPal")` |

3 | Alice | UK | PayPal | `=CHOOSE(IF(B3="US", 2, 1), C3, "Credit Card")` |

4 | Bob | Canada | Credit Card | `=CHOOSE(IF(B4="US", 2, 1), C4, "PayPal")` |

Explanation:

- We have a table with customer locations and their preferred payment options.
- The CHOOSE function is nested with an IF function to determine the index_num based on the customer’s location.
- If the customer is from the US, it returns 2 (corresponding to their preferred payment method). Otherwise, it returns 1 (related to the alternative payment method).

✨ **Example 7: Exam Grading Scheme**

Suppose you have a table with students’ exam scores, and you want to assign grades based on different grading schemes:

A | B | C | |
---|---|---|---|

1 | Student | Score | Grade |

2 | John | 85 | `=CHOOSE(IF(B2>=90, 1, IF(B2>=80, 2, IF(B2>=70, 3, IF(B2>=60, 4, 5)))), "A", "B", "C", "D", "F")` |

3 | Alice | 75 | `=CHOOSE(IF(B3>=90, 1, IF(B3>=80, 2, IF(B3>=70, 3, IF(B3>=60, 4, 5)))), "A", "B", "C", "D", "F")` |

4 | Bob | 92 | `=CHOOSE(IF(B4>=90, 1, IF(B4>=80, 2, IF(B4>=70, 3, IF(B4>=60, 4, 5)))), "A", "B", "C", "D", "F")` |

Explanation:

- We have a table with students’ exam scores and want to assign grades based on different grading schemes.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the score.
- If the score is 90 or higher, it returns 1 (corresponding to “A”). If it’s between 80 and 89, it returns 2 (related to “B”); if it’s between 70 and 79, it returns 3 (corresponding to “C”); if it’s between 60 and 69, it returns 4 (related to “D”). Otherwise, it returns 5 (related to “F”).

✨ **Example 8: Weekly Sales Goal**

Let’s set weekly sales goals based on historical sales data and the current month:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Month | Sales | Average | Goal | Goal Status |

2 | January | $12000 | $10000 | `=CHOOSE(IF(A2="January", 2, 1), B2, C2)` | `=B2-D2` |

3 | February | $9800 | $9000 | `=CHOOSE(IF(A3="January", 2, 1), B3, C3)` | `=B3-D3` |

4 | March | $10500 | $9500 | `=CHOOSE(IF(A4="January", 2, 1), B4, C4)` | `=B4-D4` |

Explanation:

- We have a table with monthly sales data and want to set weekly sales goals based on the current month.
- The CHOOSE function is nested with an IF function to determine the index_num based on the month.
- It retrieves the sales value for the current month (January) and the average sales value for other months to set the weekly sales goal.
- The goal status is calculated by subtracting the actual sales from the goal.

✨ **Example 9: Project Progress Status**

Let’s determine the project progress status based on the completion percentage and project deadline:

A | B | C | D | |
---|---|---|---|---|

1 | Project | Status | Deadline | Progress Status |

2 | Project A | On track | 90% | `=CHOOSE(IF(B2="On track", IF(C2>=90, 3, IF(C2>=75, 2, 1)), 4), "Green", "Yellow", "Orange", "Red")` |

3 | Project B | Delayed | 40% | `=CHOOSE(IF(B3="On track", IF(C3>=90, 3, IF(C3>=75, 2, 1)), 4), "Green", "Yellow", "Orange", "Red")` |

4 | Project C | On track | 60% | `=CHOOSE(IF(B4="On track", IF(C4>=90, 3, IF(C4>=75, 2, 1)), 4), "Green", "Yellow", "Orange", "Red")` |

Explanation:

- We have a table with project statuses, completion percentages, and deadlines.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the project status and completion percentage.
- If the project is “On track,” it evaluates the progress status based on the completion percentage. If it’s above 90%, it returns 3 (corresponding to “Green”). If it’s between 75% and 90%, it returns 2 (related to “Yellow”). If it’s below 75%, it returns 1 (related to “Orange”).
- If the project is “Delayed,” it returns 4 (corresponding to “Red”).

✨ **Example 10: Customer Segment Categorization**

Let’s categorize customers into different segments based on their purchase frequency and total spending:

A | B | C | D | |
---|---|---|---|---|

1 | Customer | Frequency | Total Spending | Segment |

2 | John | High | $1500 | `=CHOOSE(IF(B2="High", IF(C2>=1000, 3, IF(C2>=500, 2, 1)), 4), "VIP", "Regular", "Occasional", "New")` |

3 | Alice | Regular | $800 | `=CHOOSE(IF(B3="High", IF(C3>=1000, 3, IF(C3>=500, 2, 1)), 4), "VIP", "Regular", "Occasional", "New")` |

4 | Bob | Occasional | $300 | `=CHOOSE(IF(B4="High", IF(C4>=1000, 3, IF(C4>=500, 2, 1)), 4), "VIP", "Regular", "Occasional", "New")` |

Explanation:

- We have a table with customer data, including purchase frequency and total spending.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the customer’s segment.
- If the customer has a “High” frequency and total spending is at least $1000, it returns 3 (corresponding to “VIP”). If total spending is at least $500, it returns 2 (corresponding to “Regular”). Otherwise, it returns 1 (related to “Occasional”).
- If the customer’s frequency is not “High,” it returns 4 (corresponding to “New”).

✨ **Example 11: Product Discount Calculation**

Let’s calculate discounts for products based on their category and total purchase quantity:

A | B | C | D | |
---|---|---|---|---|

1 | Product | Category | Quantity | Discount |

2 | Product A | Electronics | 20 | `=CHOOSE(IF(B2="Electronics", IF(C2>=20, 3, IF(C2>=10, 2, 1)), IF(C2>=30, 4, 5)), 10, 5, 2, 0, 0)` |

3 | Product B | Furniture | 8 | `=CHOOSE(IF(B3="Electronics", IF(C3>=20, 3, IF(C3>=10, 2, 1)), IF(C3>=30, 4, 5)), 10, 5, 2, 0, 0)` |

4 | Product C | Electronics | 35 | `=CHOOSE(IF(B4="Electronics", IF(C4>=20, 3, IF(C4>=10, 2, 1)), IF(C4>=30, 4, 5)), 10, 5, 2, 0, 0)` |

Explanation:

- We have a table with product data, including the category and purchase quantity.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the product category and quantity.
- If the product is in the “Electronics” category and the quantity is at least 20, it returns 3 (corresponding to a 10% discount). If the amount is at least 10, it returns 2 (corresponding to a 5% discount). Otherwise, it returns 1 (corresponding to a 2% discount).
- If the product is not in the “Electronics” category, it returns 4 (corresponding to a 0% discount).

✨ **Example 12: Priority Assignment**

Let’s assign priorities to tasks based on their due dates and urgency:

A | B | C | D | |
---|---|---|---|---|

1 | Task | Due Date | Urgency | Priority |

2 | Task A | 2023-07-25 | High | `=CHOOSE(IF(B2-TODAY()<=2, IF(C2="High", 1, IF(C2="Medium", 2, 3)), IF(C2="High", 4, IF(C2="Medium", 5, 6))), "Urgent - High", "Urgent - Medium", "Urgent - Low", "High", "Medium", "Low")` |

3 | Task B | 2023-07-29 | Medium | `=CHOOSE(IF(B3-TODAY()<=2, IF(C3="High", 1, IF(C3="Medium", 2, 3)), IF(C3="High", 4, IF(C3="Medium", 5, 6))), "Urgent - High", "Urgent - Medium", "Urgent - Low", "High", "Medium", "Low")` |

4 | Task C | 2023-08-02 | Low | `=CHOOSE(IF(B4-TODAY()<=2, IF(C4="High", 1, IF(C4="Medium", 2, 3)), IF(C4="High", 4, IF(C4="Medium", 5, 6))), "Urgent - High", "Urgent - Medium", "Urgent - Low", "High", "Medium", "Low")` |

Explanation:

- We have a table with tasks, due dates, and urgency levels.
- The CHOOSE function is nested with multiple IF functions to determine the index_num based on the task’s due date and urgency level.
- If the task is due within 2 days and has high urgency, it returns 1 (corresponding to “Urgent – High”). If it has medium speed, it returns 2 (related to “Urgent – Medium”). Otherwise, it returns 3 (corresponding to “Urgent – Low”).
- If the task is not due within 2 days and has high urgency, it returns 4 (corresponding to “High”). If it has medium speed, it returns 5 (related to “Medium”). Otherwise, it returns 6 (corresponding to “Low”).

✨ **Part 3. Tips and Tricks:**

- When using the CHOOSE function with nested IF functions, ensure you have handled all possible scenarios to avoid errors.
- Always double-check the index_num to ensure it is within the valid range of values provided.
- Consider using named ranges or cell references instead of hardcoding values in the CHOOSE function to make the formula more flexible and maintainable.
- Use the CHOOSE function when you have limited options and want to choose one based on a specific condition.
- Remember that the CHOOSE function is unsuitable for large datasets or complex conditions; in such cases, consider using other lookup functions like VLOOKUP or INDEX-MATCH.