How-to-use-CHOOSE-Function-in-Excel with 06 best examples

How to use CHOOSE Function in Excel with 06 Best Examples

By

The CHOOSE function is very useful in advanced Excel which returns the specific value from a list of values supplied as arguments. 

The CHOOSE function is similar to the INDEX function in its simplest format. But, rather than an item being chosen from an array, the item is chosen from the list of arguments within the function.  


I. Syntax of CHOOSE function

Just type a few letters ‘cho‘ and select the CHOOSE function from the given suggestion list with the help of a down arrow (↓), if required.  

Syntax of the CHOOSE function_1

Then press the ‘Tab’ key, CHOOSE syntax appears with the open parenthesis in the VLOOKUP function. 

Syntax of the CHOOSE function

II. Arguments for CHOOSE Function

  • index_num – [required] the position of the value to return. It can be any number between 1 and 254, a cell reference (like A2, A3, etc.), or another formula (like RANDBETWEEN(3,7)).
  • value1 – [required] the first value from which to choose.
  • value2 – [optional] the second value from which to choose.
  • value3 – [optional] the third value from which to choose….so on till 254.

value1, [value2], [value3],…: This can be the number (like 1,2,3,4,5), cell reference (like A2, B2, C2), ranges (A2:A10, B2:B10,C2:C10), text (‘January’, ‘February’,’ March’) or a formula.

 The maximum number of values that can be provided is 254. The number of values provided should be ≥ index_value i.e., the value to choose. If the index_value is 3, then there should be at least three values: value1, value2, and value3. Otherwise, the formula returns an error #VALUE.


III. The CHOOSE function in Excel returns a value based on the index_num Argument

For a basic understanding of CHOOSE function, examples are given below:

CHOOSE function returns a value based on the index_num argument-1

=CHOOSE(4,”Jan”,”Feb”,”Mar”,”Apr“,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”) will return Apr.

 

Similarly, =CHOOSE(12,”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec“) will return Dec.

CHOOSE function returns a value based on the index_num argument-3

If the index_number is greater than the provided values, the function will give an error #VALUE!

In the above example, we have taken 12 months list. According to that index_number will be considered in between the value of 1 to 12.  If we put index_number more than that, such as 13, then the CHOOSE function will return an error #VALUE!. 

 

CHOOSE function returns a value based on the index_num argument-2

 


IV. The CHOOSE function in Excel returns a Custom Day/Month name from Date

CHOOSE function returns a custom Day and Month name from Date

 

If we want to get a weekday name and/or a month name against a date, should use the CHOOSE function in the following way:

  • To get a Weekday name against Date in cell C3

=CHOOSE(WEEKDAY(B3),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)

 

  • To get a Month name against Date in cell D3

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

 

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. 

Here B3 is the first cell of date. After applying the CHOOSE formula in cells C3 and D3, we can copy the formula till the end of the range by using Excel shortcut Alt+E+S+R or Alt+Ctrl+V+R (select the ‘Formulas and number formats‘ under the “Paste Special” dialog box). 

Copy the formula till the end of the range_CHOOSE function

 

Convert all the Formulas into the values:

We should convert all the formulas into the values in two ways: 

• Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box; 

• Or, Using the ‘Values‘ option in the ‘Paste Special’ dialog box.

Copy the cells or ranges (i.e., C3:D19) with formula by using the Excel shortcut Ctrl+C  either select ‘Values and number formats‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U or select ‘Values‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+V or Alt+Ctrl+V+V  press Enter or click on OK.

Convert all the Formulas into the values_CHOOSE function

 


V. Excel CHOOSE function returns a value based on Conditions || The Best alternative of Nested IF function

 

We generally use nested IF function to get values based on suggested conditions, but the CHOOSE function is the best alternative method to get the result.    

For example, if we want to label the result based on the marks obtained by students, we should follow the formula:   

CHOOSE function returns a value based on Conditions-1
CHOOSE function returns a value based on Conditions-2

 

With the obtained marks in B2, the formula is written in cell C2 as follows: 

 

=CHOOSE(($B2>=0) + ($B2>=30) + ($B2>=45) + ($B2>=60) + ($B2>=80) +($B2>=90), “Fail”, “Poor”, “Satisfactory”, “Good”, “Excellent”, “Star Performer”)

 

• Always arrange the conditions inside CHOOSE function in ascending order (i.e., The criteria value starts from lowest to highest). In the below example, the lowest value starts from 0 (range is 0-29) and the highest value starts from 90 (range is 90-100).

• Alternatively, we can use cell reference as criteria and get the same result. Thus, the formula becomes more dynamic. 

=CHOOSE(($B2>=0)+($B2>=30)+($B2>=45)+($B2>=60)+($B2>=80)+($B2>=90), $G$8, $G$7, $G$6, $G$5, $G$4, $G$3)

 

  • $G$8 – use absolute cell reference and refers to the cell containing ‘Fail’.
  • $G$7 – use absolute cell reference and refers to the cell containing ‘Poor’.
  • $G$6 – use absolute cell reference and refers to the cell containing ‘Satisfactory’.
  • $G$5 – use absolute cell reference and refers to the cell containing ‘Good’.
  • $G$4 – use absolute cell reference and refers to the cell containing ‘Excellent’.
  • $G$3 – use absolute cell reference and refers to the cell containing ‘Star Performer’.

 

• Fix the cell reference using the dollar sign ($) by pressing the F4 key

• Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. 

We can copy the formula till the end of the range by using Excel shortcut Alt+E+S+R or Alt+Ctrl+V+R (select the ‘Formulas and number formats‘ under the “Paste Special” dialog box). 

 

Convert all the Formulas into the values:

We should convert all the formulas into the values in two ways: 

• Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box; 

• Or, Using the ‘Values‘ option in the ‘Paste Special’ dialog box.

Copy the cells or ranges (i.e., C2:C8) with formula by using the Excel shortcut Ctrl+C  either select ‘Values and number formats‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U or select ‘Values‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+V or Alt+Ctrl+V+V  press Enter or click on OK.

Note: If we arrange the conditions inside CHOOSE function in descending order (i.e., The criteria value starts from highest to lowest) then the formula does not work properly and gets the wrong/inappropriate/incorrect output. Below example is given for reference:

CHOOSE function returns a value based on Conditions-3

VI. Excel CHOOSE function is used for Calculations based on Conditions || Best alternative of Nested IF function

 

We can use Excel CHOOSE function to calculate the database based on conditions. It is also considered as the best alternative of the Nested IF function.  

As in the given example, we can calculate the commission of each sales manager based on their sales.

Excel CHOOSE function is used for Calculations based on Conditions-1
Excel CHOOSE function is used for Calculations based on Conditions-2

With the sales amount in B2, we put the formula in C2 as follows: 

=CHOOSE(($B2>=0) + ($B2>=501) + ($B2>=1001) + ($B2>=1501) + ($B2>=2001) +($B2>=2501), $B2*10%, $B2*15%, $B2*20%, $B2*25%, $B2*30%, $B2*40%)

 

• Always arrange the conditions inside CHOOSE function in ascending order (i.e., The criteria value starts from lowest to highest). In the below example, the lowest value starts from 0 (range is $0-$500) and the highest value starts from 2501 (range is above $2501).

• Alternatively, we can use cell references as criteria and get the same result. Thus, the formula becomes more dynamic. 

=CHOOSE(($B7>=0)+($B7>=501)+($B7>=1001)+($B7>=1501)+($B7>=2001)+($B7>=2501),$B7*$G$8,$B7*$G$7,  $B7*$G$6,$B7*$G$5,$B7*$G$4,$B7*$G$3)

 

    • $G$8 – use absolute cell reference and refers to the cell containing commission criteria 10%.
    • $G$7 – use absolute cell reference and refers to the cell containing commission criteria 15%.
    • $G$6 – use absolute cell reference and refers to the cell containing commission criteria 20%.
    • $G$5 – use absolute cell reference and refers to the cell containing commission criteria 25%.
    • $G$4 – use absolute cell reference and refers to the cell containing commission criteria 30%.
    • $G$3 – use absolute cell reference and refers to the cell containing commission criteria 40%.

 

• Fix the cell reference using the dollar sign ($) by pressing the F4 key

• Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well. 

• We can copy the formula till the end of the range by using Excel shortcut Alt+E+S+R or Alt+Ctrl+V+R (select the ‘Formulas and number formats‘ under the “Paste Special” dialog box). 

 

Convert all the Formulas into the values:

We should convert all the formulas into the values in two ways: 

• Using the ‘Values and number formats‘ option in the ‘Paste Special’ dialog box; 

• Or, Using the ‘Values‘ option in the ‘Paste Special’ dialog box.

Copy the cells or ranges (i.e., C2:C8) with formula by using the Excel shortcut Ctrl+C  either select ‘Values and number formats‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+U / Alt+Ctrl+V+U or select ‘Values‘ option in the Paste Special dialog box by Excel shortcut Alt+E+S+V or Alt+Ctrl+V+V  press Enter or click on OK.


VII. Excel CHOOSE function with the VLOOKUP function is performing Reverse Lookup & Both-way Lookup

Excel Reverse VLOOKUP – is used to VLOOKUP to the left and is a useful formula in data analysis and MIS preparation.     

The VLOOKUP function searches value only to the right, but the VLOOKUP with CHOOSE function can look up in both directions from the lookup column – VLOOKUP to the left and VLOOKUP to the right. This feature in Excel is called both-way lookup or two-way lookup.

However, both of these nested functions able to retrieve values from the left of the lookup column are called the reverse lookup or backward lookup, which means a reverse lookup is a part of the both-way lookup. 

VLOOKUP and CHOOSE nested function is more flexible than VLOOKUP and performs Reverse VLOOKUP

Learn-More-2


VIII. Excel CHOOSE function with the VLOOKUP function is retrieving data from the Multiple Criteria

 

VLOOKUP Multiple Criteria means VLOOKUP formula searches the lookup_value from the multiple columns or multiple cells or multiple ranges and retrieves a value based on the all criteria matches.

VLOOKUP function is one of the most used and most powerful functions for lookup in Advanced Excel. But one of the limitations of this function is it only works for looking up data having a single criterion. Means VLOOKUP formula searches the lookup_value against the first column (single criterion), not from the multiple columns (multiple criteria).

Excel CHOOSE function with VLOOKUP is used for retrieving data/values from multiple criteria in Advanced Excel. Whereas VLOOKUP only works in single criteria. However, both functions make an array formula or non-array formula that works with multiple criteria. Learn-More-2


Conclusion

• The index_value can vary between 1 to 254.

• The number of values can also vary from 1 to 254.

• VALUE! Error – Occurs when:

  • The number of values should be equal or more than the index_num but if it is less than index_num will return a #VALUE error, that means always index_num ≥ values.
  • If the index_num is less than 1, the formula returns an error #VALUE!
  • The given index_num argument is non-numeric.

• #NAME? Error – This occurs when the value arguments are text values that are not enclosed in quotes and are not valid cell references.

• Values can be the cell reference (A2, B2, C2), or the ranges (A2:A10, B2:B10, C2:C10, etc), text (‘January’, ‘February’,’ March’ etc.), or a formula.

• CHOOSE formula returns different values based on conditions, but inside the formula, conditions should be arranged in ascending order.

• CHOOSE function will not retrieve the value from a range or array constant (such as A2:C10).


Read-More-5

• 03 Types of Excel Cell Reference: Relative, Absolute & Mixed

• 03 Best Ways: Excel REVERSE VLOOKUP | VLOOKUP to the left |

• 05 Best Ways: VLOOKUP Multiple Criteria in Excel

• 90+ Best Excel Shortcut Keys and Hotkeys with CTRL Key

• 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts


Follow AEF
Sudipta Mondal is a writer, author as well as founder of "Advance Excel Forum (AEF)". It is the best learning platform on Advance Excel.

According to Sudipta, "Advanced Excel is a very necessary modern tool in the advanced world that can only be achieved through a committed focus and self-analyzing skills".
Sudipta Mondal
Follow AEF
How to use CHOOSE Function in Excel with 06 Best Examples

The CHOOSE function is very useful in advanced Excel which returns the specific value from a list of values supplied as arguments. 

Editor's Rating:
5

Leave a Comment

You may also like