If you are to use Excel regularly, you may have come across the CHOOSE function. The function works on very basic principles. It returns a value from a list using a position from an index.
Although in Excel 2003 and earlier versions the CHOOSE function was very limited to only 29 values, the subsequent versions of Excel have increased the range to 254.
In this guide, we will go into the details of the CHOOSE function, how it works, and where you can use it.
So, let’s get to it, shall we?
What is the CHOOSE Function in Excel?
On a very basic note, CHOOSE function basically chooses and returns a value from a list based on its given position. So, if you have a list of things and you want Excel to choose a value from the list based on its position you can use the CHOOSE function. But you can use CHOOSE some creative ways with other functions to achieve some nifty results.
So, to use the CHOOSE function in Excel you have to have an index number represented in Excel as index_num. This number will refer to subsequent positions in a given list. But the CHOOSE function can only return a value from a list as long as it is within 1 to 254. So, for the CHOOSE function to work you have to ask for a list item within this limit.
To simplify this, for example, say you have a list of 10 colors:
If you use CHOOSE to call index number 5 of this list, CHOOSE will return Purple. Similarly, if you use CHOOSE to call index number 3, CHOOSE will return pink. Simple enough, right? This index number for CHOOSE is represented in Excel with the index_num argument. And for the CHOOSE function in Excel, this index_num can go up to 254. If you’re confused, don’t worry! We will show you this in Excel with pictures.
But the CHOOSE function will work differently for some select cases. For example, if you give an index number that is less than 1, maybe a 0 or a negative number, then the CHOOSE will return a #VALUE! Error. This will also be the case if your index number is more than the last value on the list.
So, for example, for our color list example, if your index number for CHOOSE function is 11, then CHOOSE will return a #VALUE! Error. Again, if the index number is a fraction, then CHOOSE will truncate it to the lowest integer.
Things You Should Know About CHOOSE Function in Excel
Here are 8 facts you have to know about the CHOOSE function for an error-free operation:
1. You must provide the CHOOSE function with an index_num to select a value from the list.
2. The CHOOSE function has a hard limit of choosing a value between 1 to 254. You cannot get a value over 254.
3. If your given index_num is lower than 1 then CHOOSE will return a #VALUE! Error. So, make sure to have an index_num higher than 1.
4. If your index_num is more than the last value of the list, you will also get a #VALUE! Error. So, ensure that you have your index_num within the last value of the list.
5. If the index_num is a fraction then CHOOSE will refer the to lowest integer.
6. CHOOSE can work with different types of data. That is CHOOSE can return values from lists that contain different types of data. The list inputs can be Cell references, Functions, texts, formulas, and defined names.
7. If you are putting texts as list items, you will need to put inverted commas (“text”) for the items. Otherwise, you will get a #NAME? error.
8. CHOOSE function in Excel will not return any value from a range. And will return a #VALUE! Error.
Syntax and Breakdown
The Syntax for the Excel CHOOSE function is:
=CHOOSE(index_num, value1, [value2], …)
Looks complex? Don’t worry. The syntax is actually pretty simple. Here is a breakdown:
index_num: The index number, a value to choose from 1 to 254.
value1: The first value of a list from which to choose.
value2: The second value of a list from which to choose.
For the CHOOSE function, the first argument is the indes_num. As we discussed before, this is a number that will refer to positions in your given list of values. This is a mandatory argument and CHOOSE will not work without this argument. This is the number that you will provide for CHOOSE to pick an item from a list.
Next, the value1 argument is the first value from a list of items from where your CHOOSE function will choose a value. This is also a mandatory argument and CHOOSE will require at least one value. From value2 onwards, all the values are optional and depend on your list. But as we have discussed before you can only have 254 such values in your list.
How to Use the CHOOSE Function: With Examples
Now we will show you how you can use the CHOOSE function in EXCEL with images. Although the basic task that CHOOSE can do may sound simple enough, you can achieve some complex tasks if you use CHOOSE with some other functions. Let’s have a look at some of the use cases of Excel CHOOSE function:
For Simple List Items
For simple list items, you can use the following process:
1. Choose any cell in your Excel. In the formula bar type in
=CHOOSE()
2. In the parenthesis type in the index_num, the number of the list item you want to find out. In our case, we used 2 as an example as we wanted to find out the 2nd item in the list.
3. Now put a comma and keep adding your list items. We followed our color example and added the color names as simple list entries as seen in the picture.
4. Then, just press enter and CHOOSE function will select the 2nd item in the list, which is Indigo.
5. As you can notice, we used inverted commas for our list items. This is only the case if you are using text items. If you are using numbers, you can just simply write the numbers.
But if you don’t use inverted commas for text items you will get a #NAME! Error as seen in the image below.
For Items In an Array or Range
You may have values or items listed in an Array or a Range and want to use the CHOOSE function to pick an item from the range. In this case, if you wrote a formula selecting the range directly by dragging your mouse you will get an error.
So, what can you do to find a list item within this range of colors? Just type in the cell number as a list item in the range following this process:
1. Select any cell other than your data range and in the formula bar type in
=CHOOSE()
2. In the parenthesis, enter the index number that you want to find out. In our case it is 2.
3. Then put a comma and type in the cell numbers separated by commas like this image:
And in the output, you will get the 2nd item in the cell range.
Using CHOOSE To Get Conditional Values
You can also use CHOOSE to return a value based on conditions. You can use this as a substitute for nested IFs. For example, say you have a list of students and their scores on an exam. You have to put a comment on their scores based on the following conditions:
This can be done if you manually use IF formulas for the remarks. For example, let’s take this sample data of 4 students and their marks.
To put a remark on their mark we can use Nested IFs using this formula:
=IF(B2>=80,”Excellent”, IF(B2>=61, “Good”, IF(B2>=41, “Satisfactory”, “Poor”)))
But with the choose function, you can just give the conditions to Excel for the CHOOSE function to work. We can use the following formula for the B3 as shown in the picture:
=CHOOSE((B3>0) + (B3>=41) + (B3>=61) + (B3>=81), “Poor”, “Satisfactory”, “Good”, “Excellent”)
This will provide you with the desired remarks as CHOOSE checks the conditions.
As you can see in the picture, the CHOOSE function works similar to the nested IF function.
But if you take a closer look, you will see that we have used a lot of Plus (+) signs. These are all part of the index_num argument of the CHOOSE function of Excel. In the index_num argument,
CHOOSE evaluates each of the conditions and returns a TRUE or FALSE value for if the condition has been met or not. CHOOSE assigns 1 for the TRUE value and 0 for the FALSE value. So, for our B3 cell, our formula for CHOOSE will translate to something like this:
=CHOOSE((1+1+1+1), “Poor”, “Satisfactory”, “Good”, “Excellent”)
If we do the addition in this formula, we will get:
=CHOOSE(4, “Poor”, “Satisfactory”, “Good”, “Excellent”)
And from here, it is a simple task for the CHOOSE function, as we have seen in the previous part of this guide.
CHOOSE to Generate Random Data
Often we may come across a use case where we need to generate random data in a cell from a range of data. We can also use the CHOOSE function to generate random data.
For example, If we want to generate random data in the A column between Strongly Disagree, Disagree, Neutral, Agree, and Disagree, we can just type in the following formula:
=CHOOSE(RANDBETWEEN(1,5), “Strongly Disagree”, “Disagree”, “Neutral”, “Agree”, “Strongly Agree”)
As we can see in the picture, in each of the cells we put the formula in, the formula returned random values from the list.
The RANDBETWEEN(1,5) function in this case selects a random number between 1 and 5 and chooses that numbered item from the list provided in our case which was “Strongly Disagree”, “Disagree”, “Neutral”, “Agree”, “Strongly Agree”
With Other Functions
CHOOSE can also be used as an argument for other functions. This allows you to use CHOOSE with other functions to get some cool results, which you cannot achieve with only CHOOSE. Here are some such examples where you can use CHOOSE with other functions.
Used with SUM
We can use CHOOSE as an augment for SUM to provide a SUM function with a range or Array. This is because every value in the argument is evaluated when CHOOSE is evaluated.
For this example, we put another column with an imaginary number of kids that preferred the range of colors we used for the demonstration. Now if we wanted to get the SUM of the number of kids in the C column we can use this process:
1. Select any cell other than your data and in the formula bar type in
=SUM(CHOOSE())
2. In the parenthesis right next to the CHOOSE function put in the index number of the cell range you want to take your value from.
3. In our case, we wrote
=SUM(CHOOSE(2, B2:B11 , C2:C11)
We wanted to choose the 2nd range of the list of ranges provided which is the column containing the number of kids that liked this color.
Now, CHOOSE returns the reference range C2:C11 and SUM provides the summation of the range C2:C11 as shown in the output image:
Used with VLOOKUP
If you have seen our VLOOKUP guide, you know that VLOOKUP can only work with the left-most column in your data. But, you may need to return a value to the left of the lookup column.
For example, we have a list of numbers in column A which is the scores of students in column B. We want to retrieve a particular student’s number if we put the student’s name in the E1 cell.
For the regular VLOOKUP formula, used in this case:
=VLOOKUP(E1,A2:B5,1,FALSE)
The formula will just return an #N/A error.
But, if we use CHOOSE as a nested element VLOOKUP to fix this. For that, we can use this formula:
=VLOOKUP(E1,CHOOSE({1,2}, B2:B5, A2:A5),2,FALSE)
If we put any name in the E1 Cell, now we will get the number corresponding to their name in the A column.
You will notice that we put 1,2 in curly brackets ({}) as our index number. This is to tell Excel that B is column 1 and A is column 2. This switch allows VLOOKUP to lookup to the left.
To Finish Up
In this guide, we talked about the CHOOSE function in detail and discussed some of the ways you can use the function. As you can see, although the basic task this function perform is simple, you can use it to perform complex tasks when coupled with other functions.
You can use the CHOOSE function to perform any of the tasks that we showed you in the guide. You can choose random values from a list, or can get values based on a certain condition with CHOOSE. You can also, use it to perform simple tasks such as getting your desired value from a range of references.
The possibilities are limitless. So, just practice the methods we’ve shown you here and become a pro at using CHOOSE in Excel.
Similar Post: