How to Use CHOOSE Function in Excel (4 Ways)

How to Use CHOOSE Function in Excel (4 Ways)

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.

Download the Practice Workbook

CHOOSE Function in Excel (Quick View)

CHOOSE Function in Excel (Quick View)

Excel CHOOSE Function: Syntax & Arguments

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.

Return Several Values Based on Condition Using CHOOSE Function

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”.

Enter formula using CHOOSE function

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.

Do Various Calculations Based on Condition Using CHOOSE Function

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.

Enter formula using CHOOSE function

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.

Generate Random Data Using CHOOSE Function

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%”.

Enter formula using CHOOSE and RANDBETWEEN functions

[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.

CHOOSE Formula to Do a Left VLOOKUP

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.

Formula using VLOOKUP and CHOOSE functions

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

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.

CHOOSE Formula to Return Next Working Day

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.

Formula using CHOOSE WEEKDAY and TODAY functions

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.

Return a Custom Day or Month Name from a Date Using CHOOSE Formula

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

Formula using CHOOSE and WEEKDAY functions

For Month Column

Formula using CHOOSE and MONTH

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo