Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Excel CHOOSE function

doc choose function 1

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.
doc choose function 2


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.
doc choose function 3

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
doc choose function 4


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:
doc choose function 5

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
doc choose function 6


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
doc choose function 7


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:
doc choose function 8

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.
doc choose function 9


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.
doc choose function 10


Ex7 – Return to next workday/weekend date based on today

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:
doc choose function 10

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
doc choose function 10


Sample File
doc sample file


Excel Productivity Tools

300 Advanced Features Help 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 70% time.

  • 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.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Office Tab Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.