# CHOOSE Function in Excel

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:

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

ABC
1SalesPerformanceColor
2\$2000Good`=CHOOSE(IF(B2="Excellent", 3, IF(B2="Good", 2, IF(B2="Satisfactory", 1, 0))), "Red", "Yellow", "Green")`
3\$3500Excellent`=CHOOSE(IF(B3="Excellent", 3, IF(B3="Good", 2, IF(B3="Satisfactory", 1, 0))), "Red", "Yellow", "Green")`
4\$1200Satisfactory`=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

ABCD
1EmployeeSalesTarget SalesBonus Amount
2John\$12000\$10000`=CHOOSE(IF(B2>=C2*1.2, 3, IF(B2>=C2, 2, IF(B2>=C2*0.9, 1, 0))), 300, 200, 100, 0)`
3Alice\$9000\$8000`=CHOOSE(IF(B3>=C3*1.2, 3, IF(B3>=C3, 2, IF(B3>=C3*0.9, 1, 0))), 300, 200, 100, 0)`
4Bob\$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

ABC
1ProductPricePrice Tier
2Product A\$15`=CHOOSE(IF(B2>=20, 3, IF(B2>=10, 2, 1)), "High", "Medium", "Low")`
3Product B\$8`=CHOOSE(IF(B3>=20, 3, IF(B3>=10, 2, 1)), "High", "Medium", "Low")`
4Product 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:

ABCD
1Order TotalMethodShipping CostSelected Method
2\$200Standard\$10`=CHOOSE(IF(A2>=100, 2, 1), B2, "Express")`
3\$120Express\$15`=CHOOSE(IF(A3>=100, 2, 1), B3, "Standard")`
4\$80Standard\$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:

ABC
1LocationTempWeather
2City A30°C`=CHOOSE(IF(B2>30, 3, IF(B2>20, 2, 1)), "Hot", "Warm", "Mild")`
3City B18°C`=CHOOSE(IF(B3>30, 3, IF(B3>20, 2, 1)), "Hot", "Warm", "Mild")`
4City C25°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:

ABCD
1CustomerLocationPaymentSelected Payment
2JohnUSCredit Card`=CHOOSE(IF(B2="US", 2, 1), C2, "PayPal")`
3AliceUKPayPal`=CHOOSE(IF(B3="US", 2, 1), C3, "Credit Card")`
4BobCanadaCredit 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).

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

ABC
2John85`=CHOOSE(IF(B2>=90, 1, IF(B2>=80, 2, IF(B2>=70, 3, IF(B2>=60, 4, 5)))), "A", "B", "C", "D", "F")`
3Alice75`=CHOOSE(IF(B3>=90, 1, IF(B3>=80, 2, IF(B3>=70, 3, IF(B3>=60, 4, 5)))), "A", "B", "C", "D", "F")`
4Bob92`=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:

ABCDE
1MonthSalesAverageGoalGoal Status
2January\$12000\$10000`=CHOOSE(IF(A2="January", 2, 1), B2, C2)``=B2-D2`
3February\$9800\$9000`=CHOOSE(IF(A3="January", 2, 1), B3, C3)``=B3-D3`
4March\$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:

ABCD
2Project AOn track90%`=CHOOSE(IF(B2="On track", IF(C2>=90, 3, IF(C2>=75, 2, 1)), 4), "Green", "Yellow", "Orange", "Red")`
3Project BDelayed40%`=CHOOSE(IF(B3="On track", IF(C3>=90, 3, IF(C3>=75, 2, 1)), 4), "Green", "Yellow", "Orange", "Red")`
4Project COn track60%`=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:

ABCD
1CustomerFrequencyTotal SpendingSegment
2JohnHigh\$1500`=CHOOSE(IF(B2="High", IF(C2>=1000, 3, IF(C2>=500, 2, 1)), 4), "VIP", "Regular", "Occasional", "New")`
3AliceRegular\$800`=CHOOSE(IF(B3="High", IF(C3>=1000, 3, IF(C3>=500, 2, 1)), 4), "VIP", "Regular", "Occasional", "New")`
4BobOccasional\$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:

ABCD
1ProductCategoryQuantityDiscount
2Product AElectronics20`=CHOOSE(IF(B2="Electronics", IF(C2>=20, 3, IF(C2>=10, 2, 1)), IF(C2>=30, 4, 5)), 10, 5, 2, 0, 0)`
3Product BFurniture8`=CHOOSE(IF(B3="Electronics", IF(C3>=20, 3, IF(C3>=10, 2, 1)), IF(C3>=30, 4, 5)), 10, 5, 2, 0, 0)`
4Product CElectronics35`=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:

ABCD
2Task A2023-07-25High`=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")`
3Task B2023-07-29Medium`=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")`
4Task C2023-08-02Low`=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.