How to Apply CHOOSE Function to Create Drop-Down List in Excel

A drop-down list is a feature in Microsoft Excel that allows to create a list of items to select from. This can be helpful when we have a list of items that we need to choose from, and we don’t want to have to type them all out. In this article, I will try to explain 3 simple ways to create drop-down list with CHOOSE function in Excel. I hope it will be very helpful for you if you are looking for the way to create drop-down list with CHOOSE Function in Excel.


Download Practice Workbook


3 Simple Ways to Create Drop-Down List Using CHOOSE Function in Excel

1. Apply CHOOSE Function to Create a Dependent Drop-Down List

We can create a drop-down list with specific formula for some specific values. In order to create a dependent drop-down list, we can use CHOOSE function. The whole procedure is described in the following section.

Steps:

  • First of all, create an organised dataset. For me, I have created a dataset of weekdays with a specific number in Day Number and Day Name columns.

Excel CHOOSE Function Drop Down List

  • Now, select a cell (i.e. B14) to create a drop-down list.
  • Next, go to the Data tab.
  • From the ribbon, click on Data Tools.
  • After that, pick the Data Validation option to have Data Validation wizard.

Simple Drop-Down List Creation with CHOOSE Function

  • Pick List from the Allow section from the Data Validation wizard.

  • Set range (i.e. $B$5:$B$11) in the Source section.
  • Then, press OK.

Simple Drop-Down List Creation with CHOOSE Function

Thus, we can have a drop-down list on the defined cell.

  • Next, pick a cell and apply the following formula where you want to have the result based on the drop-down selection.
=CHOOSE(B14,C5,C6,C7,C8,C9,C10,C11)

Here, the CHOOSE function returns the output based on the index-number that is mentioned by B14.

Excel CHOOSE Function Drop Down List

  • Finally, press the ENTER button and choose any option from the drop-down list to have the related result.

Excel CHOOSE Function Drop Down List

Read More: How to Use CHOOSE Function to Perform IF Condition in Excel


2. Combine CHOOSE Function with Different Arithmetic Functions to Create a Drop-Down List

We can also assign different arithmetic functions with the CHOOSE function to have resulted from the drop-down list. It’s not a difficult task to do. We will agree with me after reading the whole procedure listed below.

Steps:

  • Create an organized dataset as a first step. Here, I have arranged the salary of different footballers in Player Name, Club, and salary I have also mentioned the particulars that I want to add in the drop-down list in Matrices column.

Combining CHOOSE Function with Different Arithmetic Functions

  • Afterward, pick a cell (i.e. B18) to create a drop-down list.
  • Next, go to the Developer tab.
  • From the ribbon, click on Insert.
  • After that, pick the Combo Box (Form Control).

The drop-down box will be in the selected cell.

  • Now, right-click on the mouse and select the Format Control… option.

Combining CHOOSE Function with Different Arithmetic Functions

  • Go to Control and assign the Input range (i.e. $C$12:$C$15) and Cell Link (i.e. $B$18).
  • Then, click on OK.

  • Followingly, select a cell (i.e. C18) and apply the following formula where you want to have the result based on the drop-down selection.
=CHOOSE(B18,AVERAGE(D5:D9),MEDIAN(D5:D9),MAX(D5:D9),MIN(D5:D9))

Excel CHOOSE Function Drop Down List

  • Finally, you can have your desired result just by selecting it from the drop-down list.

Excel CHOOSE Function Drop Down List


3. Merge CHOOSE and MATCH Functions to Create a Drop-Down List

The creation of a calculator merging the CHOOSE and MATCH functions is also possible. We can add a drop-down box with different operation names. The procedure is described in the following section.

Steps:

  • As a first step, organise your data properly. I have set the operations name along with a number in the Operation and Value columns, I have also added two numbers in the dataset with which I will apply the operators.

 Combination of CHOOSE and MATCH Functions

  • Now, select a cell (i.e. C14) to create a drop-down list.
  • Next, go to the Data tab.
  • From the ribbon, click on Data Tools.
  • After that, pick the Data Validation option to have Data Validation wizard.

Excel CHOOSE Function Drop Down List

  • After that, pick List from the Allow section and set the range (i.e. $B$5:$B$11) in the Source section.
  • Press OK to finish the drop-down list creation process.

Thus, we can have operation names in a drop-down list.

  • Now, input the following formula with the MATCH Function in your preferred cell (i.e. B14) to have the match number of the operation.
=MATCH(C14,B5:B8,0)

  • Press the ENTER button to have the match number.

Excel CHOOSE Function Drop Down List

  • Along with that, pick a cell (i.e. D14) and apply the following formula where you want to have the result based on the drop-down selection.

=CHOOSE(B14,B11+C11,B11-C11,B11*C11,B11/C11)

  • Finally, hit ENTER to finish the process.

Excel CHOOSE Function Drop Down List

Thus, we can create a drop-down list with the CHOOSE and MATCH functions.

Merge CHOOSE and MATCH Functions to Create a Drop-Down List

Read More: Advanced Uses of CHOOSE Function in Excel (9 Applications)


Conclusion

That’s all for this article. In this article, I have tried to explain 3 simple ways to create drop-down list with CHOOSE function in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo