How to Use CHOOSE Function with Array in Excel

Get FREE Advanced Excel Exercises with Solutions!

We usually use the CHOOSE function to find a value from a list. In this article, we will learn to use the CHOOSE function with an array to perform different activities in Excel. Here, array means, we will either input multiple cells in the function or will get multiple results by applying the function.


Introduction to CHOOSE Function in Excel

The CHOOSE function in Excel is created to return a value from a list depending on a given position. We can combine the CHOOSE  function with other Excel functions for performing various advanced operations.

  • Syntax

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

  • Arguments

index_num: It refers to the position where the value is to be returned. It can be any value between 1 to 254, as well as a cell reference or another formula. This is a required argument.

value1: It is the first value in the list of 254 values from which the function chooses. This argument supports numbers, cell references, text values, and defined names or formulas. It is required.

[value2]: It refers to the second value in the list of 254 values from which the function chooses. This argument is optional.


Use of CHOOSE Function with Array in Excel: 7 Simple Examples

In this article, we will demonstrate 7 simple examples of using the CHOOSE function with an array in Excel. To describe the methods, we will use some datasets and show the steps with screenshots. So, without further delay, let’s get started.


1. Return Single Value in Excel Using CHOOSE Function with Array

Suppose, we have a dataset (B4:B9) in Excel that contains the names of some Participants in a lottery. Here, the Serial No. of the Champion is 2 (E4). Now, we need to find the Champion (E5) from the Participants list (B5:B9) using the CHOOSE function in Excel with the array. The steps to do so are below.

Return Single Value in Excel Using CHOOSE Function with Array

Steps:

  • To begin, go to cell E5.
  • Next, to find the name of the Champion, type the following formula in cell E5:
=CHOOSE(E4,B5,B6,B7,B8,B9)
  • Then, press the Enter key.
  • In this way, you will get the name of the Champion in cell E5 (see screenshot).

In the formula, cell E4 refers to the position of the value to return. So, cell E4 is the index_num. Besides, the cells B5, B6, B7, B8, and B9 are the values from which the function will choose the Champion. We inserted the cell names separately instead of inserting a range because the CHOOSE function does not support a range.


2. Apply CHOOSE Function with Array to Return Multiple Values in Excel

Let’s say, we have a dataset (B4:D12) in Excel containing the Names of some students and their marks in Math and Physics. However, we need to pick a student’s Name along the marks in the two subjects. Here, we will use the CHOOSE function in Excel with the array to return these multiple values. By applying the formula of this method, we will always get the values in a row even if they are in a column. The steps are below.

Apply CHOOSE Function with Array to Return Multiple Values in Excel

Steps:

  • First, select cell B12.
  • After that, to get the values in a row, enter the formula below in the cell (B12):
=CHOOSE({1,2,3},B6,C6,D6)
  • Therefore, press the Enter key.
  • Thus, you will get the values in the range B12:D12 (see the picture below).

In the formula, the cells B6, C6 & D6 denote the values to be returned. Again, {1,2,3} array refers to the positions of the values. So, {1,2,3} array is the index_num in this formula.

  • By applying this formula, we get the result in an array format.
  • If we select a value, then the other two values will automatically be selected (see screenshot).
  • To delete the entire array, you will need to select any one cell in the array (cell B12 in our case).

  • Afterward, just press the Delete button on the keyboard.
  • As a result, all three values in the array will be removed at once.
  • See the final output in the screenshot below.

Apply CHOOSE Function with Array to Return Multiple Values in Excel

Read More: How to Use Excel Formula to Choose Between Two Values


3. Excel CHOOSE Function with Array to Find Values Depending on Specific Conditions

Assuming, we have a dataset (B4:G10) in Excel. It contains the Names of some students and their Marks in the exam. It also includes the Marks distribution of Grades in the range F4:G10. Now, we need to determine the Grades (D5:D10) of the students based on their Marks in the exam. Again, we will use the Excel CHOOSE function array to find the Grades. Let’s see the steps below.

Excel CHOOSE Function with Array to Find Values Depending on Specific Conditions

Steps:

  • Firstly, activate cell D5.
  • Secondly, insert the formula below in the cell (D5) to find the Grade of Stephen:
=CHOOSE((C5>0)+(C5>=40)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80),$F$5,$F$6,$F$7,$F$8,$F$9,$F$10)
  • After inserting the formula, press the Enter key on the keyboard.
  • As a result, you will find the Grade (A+) in cell D5.

🔎 How Does the Formula Work?

  • $F$5,$F$6,$F$7,$F$8,$F$9,$F$10: Refer to the list of values to be returned. The ‘$’ sign is used here to use the formula in other cells. So, we locked these cells using this sign.
  • (C5>0)+(C5>=40)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80): It is the index_num argument that will analyze the conditions. If the condition is satisfied, it will return TRUE (1) otherwise FALSE (0). In our case, cell C5 meets the six conditions. So the formula becomes:

=CHOOSE(1+1+1+1+1+1,$F$5,$F$6,$F$7,$F$8,$F$9,$F$10)

Finally, the formula will be:

=CHOOSE(6,$F$5,$F$6,$F$7,$F$8,$F$9,$F$10)

Hence, the output will be the 6th value (A+) in the list.

  • At this time, to copy the formula in the rest of the cells put the cursor in the bottom-right corner of cell D5.
  • Consequently, you will see a plus sign (+) there.

  • Double-click on the plus sign.
  • Eventually, you will find the Grades of the rest of the students (D6:D10).
  • See the final result in the image below.

Excel CHOOSE Function with Array to Find Values Depending on Specific Conditions


4. Use Array in CHOOSE Function for Doing Calculation in Excel

In this method, we will use the array in the CHOOSE function in Excel for doing calculations. For this, we will use the dataset (B4:D9) below that contains the names of some Fruits and their Sales. The worksheet also contains the % of Sales (F5:F7) for determining the Estimated Profit (D5:D9) depending on the Sales (G5:G7). The steps for calculating the Estimated Profit using the CHOOSE function are below.

Use Array in CHOOSE Function for Doing Calculation in Excel

Steps:

  • First of all, we will calculate the Estimated Profit of Orange.
  • To do so, go to cell D5.
  • Then, type the following formula in the cell to calculate the Estimated Profit:
=CHOOSE((C5>0)+(C5>=151)+(C5>=301),C5*$F$5,C5*$F$6,C5*$F$7)
  • After pressing the Enter key, you will find the Estimated Profit of Orange in cell D5.

This formula works in the same way as the previous method. It multiplies the % of Sales ($F$5, $F$6 & $F$7) with the Sales (C5) based on their values ((C5>0)+(C5>=151)+(C5>=301)).

  • After that, double-click on the Fill Handle to auto-fill the rest of the cells (D6:D9).
  • In this way, you will get the Estimated Profit of all the Fruits (see screenshot).


5. Apply CHOOSE Function with Array for Left VLOOKUP

In this method, we will apply the CHOOSE function with the array for left VLOOKUP. We can only search the leftmost column using the VLOOKUP function. To describe this method, we will use the dataset (B4:C9) below. It contains the Marks (B5:B9) of some students in the exam and their Names (C5:C9). Suppose, we want to return the Marks of Joseph in cell F5. Here, we will first use the VLOOKUP function and observe the limitation. Then we will combine the CHOOSE function and the VLOOKUP function to fix the limitation. See the steps below.

Apply CHOOSE Function with Array for Left VLOOKUP

Steps:

  • Firstly, we will use the VLOOKUP function to find the Marks of Joseph.
  • Hence, activate cell F5.
  • Next, to get Marks of Joseph, enter the following formula:
=VLOOKUP(F4,B5:B9,1,FALSE)
  • Press the Enter key.
  • Thereupon, you will see the #N/A error (see screenshot).
  • This is because the VLOOKUP function can only search for the value in the left-most column.
  • Here, the lookup column B is located on the right side of the return column C.
  • That is why it shows an error.

  • To solve the problem, we will now assign the CHOOSE function to the VLOOKUP function.
  • First of all, we need to delete the previous formula in cell F5.
  • Next, to get the Marks of Joseph, insert the formula below in cell F5:
=VLOOKUP(F4,CHOOSE({1,2},C5:C9,B5:B9),2,FALSE)
  • Finally, press the Enter button.
  • Thus, you will get the result in cell F5 (see screenshot).

🔎 How Does the Formula Work?

  • CHOOSE({1,2},C5:C9,B5:B9): It flips the position of column C to column 1 and column B to column 2.
  • VLOOKUP(F4,CHOOSE({1,2},C5:C9,B5:B9),2,FALSE): It returns the Marks of Joseph.

6. Combination of Excel SUM & CHOOSE Functions with Array

In this approach, we will combine the SUM function and the CHOOSE function with an array in Excel. To do so, we will use the dataset (B4:E8) below containing the Names of some students and their marks in Math, Physics, and Chemistry. Let’s say, we want to sum up the Total Marks of Joseph in cell C11. The steps are below.

Combination of Excel SUM & CHOOSE Functions with Array

Steps:

  • Firstly, go to cell C11.
  • Afterward, to get the Total Marks of Joseph, insert the formula below:
=SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8))
  • Lastly, press the Enter key to get the output (see screenshot).

🔎 How Does the Formula Work?

  • CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8): Returns the Marks of Joseph in the three subjects.
  • SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8)): Adds the marks returned by the CHOOSE function.

7. Assign CHOOSE Function with Array to Return a Cell Reference

Here, we will insert the Excel CHOOSE function with an array to return a cell reference. For this method, we will use the dataset (B4:E9) below that contains the names of some countries that ordered the Fruits (G5:G7), the Order Dates, and the IDs of the fruits. Suppose, we want to insert the Fruits according to their IDs (positions) in the range E5:E9. Follow the steps below to do so.

Assign CHOOSE Function with Array to Return a Cell ReferenceSteps:

  • First, select cell E5.
  • After that, to get the fruit of ID = 2, insert the formula below in cell E5:
=CHOOSE(D5,$G$5,$G$6,$G$7)
  • Then, press Enter.
  • Consequently, you will find the output (Apple).

In the formula, cell D5 refers to the position number 2. That is why, the formula returns the second fruit (Apple) from the list ($G$5,$G$6,$G$7).

  • To auto-fill the rest of the cells, double-click on the plus sign (see screenshot).

Read More: Advanced Uses of CHOOSE Function in Excel


Things to Remember

While using the CHOOSE function, we need to always keep in mind some important things. They are below:

  • We can not use more than 254 values in this function.
  • When the index_num is less than 1 or greater than 254, the function returns the #VALUE! error.
  • If the entered index_num is a fraction, then the function rounds it to the lower integer.

Download Practice Workbook

Download the practice workbook from here.


Conclusion

I hope the above tutorial will be helpful for you in learning the use of the CHOOSE function with the array in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.


Related Articles


<< Go Back to Excel CHOOSE Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo