MS Excel’s **CHOOSE **function returns a value from a list using a given position or index. The values provided to **CHOOSE **can be hard-coded constants or cell references. This is one of those Excel functions that may not seem useful on their own but combined with other functions gives several remarkable advantages. This article will share the complete idea of how the **CHOOSE **function works in Excel independently and then with other Excel functions.

**Table of Contents**hide

## Download the Practice Workbook

**CHOOSE Function in Excel (Quick View)**

**Excel CHOOSE Function: Syntax & Arguments**

**Summary**

Chooses a value or action to perform from a list of values, based on an index number.

**Syntax**

`=CHOOSE (`

`index_num`

`, `

`value1`

`, [value2], ...)`

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

index_num |
Required | This is the argument that can be a text string or a variable that holds a text string. |

value1 |
Required | Pass the first value from which to choose. |

[value2] |
OPTIONAL | Pass the second value from which to choose. |

**How to Use CHOOSE Function in Excel (6 Ways)**

**Example 1: Return Several Values Based on Condition Using CHOOSE Function**

Let’s assume we have a dataset of student’s marks. With this, we have a grade distribution that will help us to generate grades for each student. Now our task is to print actual grades based on the result for each student.

**Step 1:** Enter the following formula in cell **G4 **and copy it down up to **G10**

`=CHOOSE((F4>0) + (F4>=50) + (F4>=60) + (F4>=70) + (F4>=80) + (F4>=90), "F", "D", "C", "B","A","A+")`

**Formula Explanation**

- Here in the
**CHOOSE**function,**((F4>0) + (F4>=50) + (F4>=60) + (F4>=70) + (F4>=80) + (F4>=90)**this is the index number. As we have multiple index numbers depending on the situation, plus (+) sign is used to make them OR operation. - Depending on the index number values, the
**CHOOSE**function will return values from**“F”, “D”, “C”, “B”,”A”,”A+”**this list. Like if the matched index number is between 70 and 79 then it will print “**B**”.

**Example 2: Do Various Calculations Based on Condition Using CHOOSE Function**

In the previous example, we have seen how to print a message based on conditions using **CHOOSE **function. Here we will check the condition as well as we will do some calculations depending on the condition.

Let’s consider a dataset of products with their prices. There is also a predefined discount distribution. Now our task is to find out the discount for each product depending on their prices.

**Step 1:** Enter the following formula in cell **F4 **and copy it down up to **F15**

`=CHOOSE((E4>0) + (E4>=651) + (E4>=801) + (E4>=1001) + (E4>=1101),"No discount ",E4*5%, E4*8%, E4*10%,E4*12%)`

**Formula Explanation**

**(E4>0) + (E4>=651) + (E4>=801) + (E4>=1001) + (E4>=1101)**these are the conditions which are used as index numbers.**“No discount “, E4*5%, E4*8%, E4*10%, E4*12%**depending on the index number, the**CHOOSE**function will select any of the values from this list.

**Example 3: Generate Random Data Using CHOOSE Function**

Sometimes we may need to generate random data for any specific task. Let’s say if we want to give a random discount for each product. For this, the **CHOOSE **function can help us. Using this function, we will insert discounts for each product randomly.

**Step 1:** Enter the following formula in cell **F4 **and copy it down up to **F15**

`=CHOOSE(RANDBETWEEN(1,5),"No Discount","5%", "8%", "10%", "12%")`

**Formula Explanation**

- In the
**CHOOSE**function,**RANDBETWEEN(1,5)**is our index number. As we want to generate numbers randomly,**RANDBETWEEN**is used to generate random numbers from 1 to 5. For more information about this**RANDBETWEEN**function, you can check this link - According to the random index number, the
**CHOOSE**function will select values from this list**“No Discount”,”5%”, “8%”, “10%”, “12%”**.

*[Note: These random values will be updated after each Enter press.]*

**Example 4: CHOOSE Formula to Do a Left VLOOKUP**

In MS Excel the **VLOOKUP **function can only search in the left-most column. But not all the time our search value may not be in the left-most column. If we try to extract data from any other column except the left-most column, **VLOOKUP **will return** #NA** error then. To avoid this problem, we could use the **CHOOSE **function in the formula.

Let assume the same dataset as the previous example. Now we will search the product’s price by entering their Model name.

**Step 1: **Enter the following formula in cell **D19 **and press **Enter**

`=VLOOKUP(D18,CHOOSE({1,2}, D4:D15, E4:E15),2,FALSE)`

**Formula Explanation**

- In the
**VLOOKUP**function,**D18**is holding the lookup value.**CHOOSE({1,2}, D4:D15, E4:E15)**this is containing the range where the value will be searched.**2**is used as the data will be extracted from the second column.**FALSE**is for an exact match. - To explore more about the VLOOKUP function, visit this link
**CHOOSE({1,2}, D4:D15, E4:E15)**here**{1,2}**this array contains the index number.**D4:D15**is the first value range and**E4:E15**is the second value range.

**Step 2:** Now enter any model name in cell **D18 **and press **Enter**

**Example 5: CHOOSE Formula to Return Next Working Day**

Let’s have an employee list with their current working day. The different employees have different current working days. Now our task is to find their next working day using the formula. For this scenario, we are considering the working day from Monday to Friday.

**Step 1: **Enter the formula in cell **D4 **and copy it down up to **D12**

`=C4+CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2)`

**Formula Explanation**

- In the
**CHOOSE**function,**WEEKDAY(TODAY())**is defining the index number using the weekend from the current date. To learn more about the**WEEKDAY**function, you can visit this link **1,1,1,1,1,3,2**this list is defining which number we should add with the current working day. Like if it is in the working day then it will add 1, today is Friday (index_num 6), you add 3 to return next Monday.- For me today is
**9/19/2021**. All the calculation is done based on this date.

**Example 6: Return a Custom Day or Month Name from a Date Using CHOOSE Formula**

Using this CHOOSE function, we can easily find out the week name or month name from any given date. Though there are some built-in functions in Excel for this kind of task, we will see here how we can do that differently with the help of **CHOOSE **function.

Let’s assume in the product information table, there is a new column named Delivery Date. Now our task is to find out the week and month name from the given date.

**Step 1: **Enter the following formula

From **G4 **to **G15 **

`=CHOOSE(WEEKDAY(F4),"Sun","Mon","Tues","Wed","Thurs","Fri","Satur")`

From **H4 **to **H15**

`=CHOOSE(MONTH(F4), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")`

For **Week** Column

For **Month** Column

**Things to Remember**

Common Errors |
When they show |
---|---|

#VALUE | If index_num is out of range, CHOOSE will return #VALUE. |

Range or Array Problem | CHOOSE will not retrieve values from a range or array constant. |

**Conclusion**

These are some ways to use the **CHOOSE **function in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.