Note: The other languages of the website are Google-translated. Back to English

## Excel CHOOSE function Description

Syntax and Arguments

Usage and Examples

### Description

The CHOOSE function returns a value from the list of value argument based on the given index number. For example, CHOOSE(3,”Apple”,”Peach”,”Orange”) returns Orange, the index number is 3, and Orange is the third value after index number in the function.

### syntax and Arguments

Formula syntax

 CHOOSE(index_num, value1, [value2], …)

Arguments

 Index_num: Required. A numeric value between 1-254 to indirect which argument value will be selected. if index_num is out of the scale between 1-254, CHOOSE returns the #VALUE! error value. If index_num is a decimal value, it will be rounded down to a whole number before formula being used. Value1: Required. The first value in the formula that can be choose. Value2: Optional. The second and up to 254th value from which to choose.

Value1, value2… can be numbers, texts, formulas, cell references, or defined name.

Return Value

The CHOOSE function returns a value from a list based on the given position.

### Usage and Examples

In this part, I list some simple but representative examples to explain the usage of the CHOOSE function.

Ex1 - Basic usage: using CHOOSE function alone to choose value from list argument

Formula1:

=CHOOSE(3,"a","b","c","d")

Return: c, which is the third argument after the index_num of 3 in the CHOOSE function.

Note: using double quotes surrounding the value if it is text.

Formula2:

=CHOOSE(2,A1,A2,A3,A4)

Return: Kate, the value of A2. Since the index_num is 2, and A2 is the second value in the CHOOSE function.

Formula3:

=CHOOSE(4,8,9,7,6)

Return: 6, the 4th list argument in the function. Ex2 – Return different results based on multiple conditions

Supposing you have a list of deviations for each product which needed to be labeled based on conditions as below screenshot shown. Usually, you can use the IF function to handle, but here I introduce how to use the CHOOSE function to solve this problem easily

Formula:

=CHOOSE((B7>0)+(B7>1)+(B7>5),"Top","Middle","Bottom")

Explain:

(B7>0)+(B7>1)+(B7>5): the index_num, B7 is 2, which is bigger than 0 and 1 but less than 5, so we get the intermediate result:

=CHOOSE(True+Ture+False,"Top","Middle","Bottom")

As we know, True = 1, False = 0, so the formula can be viewed as :

=CHOOSE(1+1+0,"Top","Middle","Bottom")

then

=CHOOSE(2,"Top","Middle","Bottom")

Result: Middle Ex3 – Return different calculated results based on conditions

Supposing you need to calculate the rebates for each product based on the amount and price as below screenshot shown: Formula:

=CHOOSE((B8>0)+(B8>100)+(B8>200)+(B8>300),B8*C8*0.1,B8*C8*0.2,B8*C8*0.3,B8*C8*0.5)

Explain:

(B8>0)+(B8>100)+(B8>200)+(B8>300): index_number, B8 is 102, which is larger than 100 but less than 201, so in this part, it returns result shown as:

=CHOOSE(true+true+false+false,B8*C8*0.1,B8*C8*0.2,B8*C8*0.3,B8*C8*0.5)

=CHOOSE(1+1+0+0,B8*C8*0.1,B8*C8*0.2,B8*C8*0.3,B8*C8*0.5)

then

=CHOOSE(2,B8*C8*0.1,B8*C8*0.2,B8*C8*0.3,B8*C8*0.5)

B8*C8*0.1,B8*C8*0.2,B8*C8*0.3,B8*C8*0.5: the values from which to choose, rebate equals to price * amount * rebate percent, since here index_num is 2, it chooses B8*C8*0.2

Returns: 102*2*0.2=40.8 Ex4 – Choose random from list

In Excel, sometimes, you may need to randomly choose a value from a given list, the CHOOSE function can solve this job.

Randomly choose one value from a list:

Formula:

=CHOOSE(RANDBETWEEN(1,5),\$D\$2,\$D\$3,\$D\$4,\$D\$5,\$D\$6)

Explain:

RANDBETWEEN(1,5): index_num, randomly obtain number between 1 to 5

\$D\$2,\$D\$3,\$D\$4,\$D\$5,\$D\$6: the list of values from which to choose Ex5 – Combine CHOOSE and VLOOKUP functions to return value in left column

Generally, we use the VLOOKUP function =VLOOKUP (value, table, col_index, [range_lookup]) to return a value based on a given value from a table range. But with the VLOOKUP function, it will return an error value while the return column is on the left of lookup column as below screenshot shown: In this case, you can combine the CHOOSE function with VLOOKUP function to solve the problem.

Formula:

=VLOOKUP(E1,CHOOSE({1,2},B1:B7,A1:A7),2,FALSE)

Explain:

CHOOSE({1,2},B1:B7,A1:A7): as table_range argument in the VLOOKUP function. {1,2} means to display 1 or 2 as index_num argument based on the col_num argument in VLOOKUP function. Here, the col_num in VLOOKUP function is 2, so the CHOOSE function display as CHOOSE(2, B1:B7,A1:A7), means to choose value from A1:A7. Ex6 – Return weekday or month based on given date

With the CHOOSE function, you also can return the relative weekday and month based on a given date.

Formula 1: return weekday by a date

=CHOOSE(WEEKDAY(),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Explain:

WEEKDAY(): the index_num argument, to get the weekday number of the given date, for example, WEEKDAY(A5) returns 6, then the index_num argument is 6.

"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday": value list arguments, start with “Sunday” because weekday number “1” indicates “Sunday”.

Formula 2: return month by a date

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

Explain:

MONTH(): index_num argument, which get the month number from the given date, for example, MONTH(A5) returns 3. In daily work, you may want to calculate the next workday or weekend based on today. Here the CHOOSE function also can do you a favor.

For instance, today is 12/20/2018, Thursday, now you need to obtain the next workday and weekend.

Formula 1: get the date of today

=TODAY()

Result: 12/20/2018

Formula 2: get the weekday number of today

=WEEKDAY(TODAY())

Result: 5 (while today is 12/20/2018)

The weekday number list as below screenshot shown: Formula 3: get the next workday

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

Explain:

Today(): return the current date

WEEKDAY(TODAY()): index_num argument in the CHOOSE function, get the weekday number of today, for example, Sunday is 1, Monday is 2…

1,1,1,1,1,3,2: value list argument in the CHOOSE function. For example, if weekday(today()) returns 1 (Sunday), it choose 1 form the list of values, then the whole formula changes to =Today()+1, which means to add 1 day to return next Monday. If weekday(today()) returns 6 (Friday), it chooses 3 from the list of values, because Friday is 3 days far from next Monday.

Result (while today is 12/20/2018):

=12/20/2018+CHOOSE(5,1,1,1,1,1,3,2)

=12/20/2018+1

=12/21/2018

Formula 4: get the next weekend day

=TODAY()+CHOOSE(WEEKDAY(TODAY()),6,5,4,3,2,1,1)

Explain:

6,5,4,3,2,1,1: value list argument in the CHOOSE function. For example, if weekday(today()) returns 1 (Sunday), it chooses 6 from the list of values, then the whole formula changes to =Today()+6, which means to add 6 days and return next Saturday.

Result:

=12/20/2018+CHOOSE(5,6,5,4,3,2,1,1)

=12/20/2018+2

=12/22/2018 ### The Best Office Productivity Tools

#### Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

• Designed for 1500 work scenarios, helps you solve 80% Excel problems.
• Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
• Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
• 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. #### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• One second to switch between dozens of open documents!
• Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
• Increases your productivity by 50% when viewing and editing multiple documents.
• Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer. 