How to Use CHOOSE function in Excel – Formula Examples
The examples below demonstrate how CHOOSE may supplement other Excel functions’ capabilities and offer different approaches to some common problems—even those that are widely regarded as impractical.
Returning different values depending on a set condition is one of Excel’s most common duties. In most circumstances, a traditional nested IF statement can be used to do this. However, using the CHOOSE feature can be a quick and simple replacement.
1. Return different values based on the condition
Supposing you have a column of Air Quality Index and you want to label the scores based on the following conditions:
- Nesting a few IF formulae inside of one another is one method for achieving this
=IF(E2>=401, "Severe", IF(E2>=301, "Very poor", IF(E2>=251, "Poor", IF(E2>=101,"Moderately Polluted", IF(E2>=51,"Satisfactory", "Good")))))
- Another option is to select a label that fits the condition
=CHOOSE((E2>0) + (E2>=51) + (E2>=101) + (E2>=251) + (E2>=351) + (E2>=401), "Good", "Satisfactory", "Moderately Polluted", "Poor", "Very Poor", "Severe")
Instead of using hardcoded labels, you can use cell references to increase the formula’s flexibility, for example:
=CHOOSE((E2>0) + (E2>=51) + (E2>=101) + (E2>=251) + (E2>=351) + (E2>=401), $A$2, $A$3, $A$4, $A$5, $A$6, $A$7)
2. Excel CHOOSE Formula for Random Data
As you are surely aware, Microsoft Excel includes a unique function called RANDBETWEEN that creates random integers between the bottom and top numbers that you select. It should be nested inside the index num argument of CHOOSE so that your formula can produce practically any kind of random data.
For Example
=CHOOSE(RANDBETWEEN(1,6), "Good", "Satisfactory", "Moderately polluted", "Poor", "Very poor", "Severe")
3. CHOOSE Function for Selecting Month
The CHOOSE Function is like a tool for picking a month form a list of months based on a date. For instance, if you have a adte in Excel and want to know which month it is, you can CHOOSE.
Let’s consider you have a list of dates in Column A, and you want to find the month for one of those dates, like the second date in cell A3.
You can use this formula: “=CHOOSE(MONTH(A4), “Jan”, “Feb”, “Mar”, “Apr”, “May”,”Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”).”
When you use this formula, it checks the month of the date in cell A4 and then gives you the corresponding months name. So, if A4 contains a date in March it will return “Mar”.
How to use CHOOSE Function in Excel
The CHOOSE function is technically part of Excel’s lookup function and can be incredibly useful. The CHOOSE function returns a value from a list using an index. One of those Excel features, CHOOSE, may not seem helpful alone, but when paired with other functions, it offers a ton of fantastic advantages.
In most basic form, the CHOOSE function allows you to select a value from a list by its position. Here we’ll see what the Excel CHOOSE Function is all about.
Contact Us