Professor Excel
choose, formula, excel

CHOOSE Formula in Excel: Everything You Should Know

The CHOOSE formula in Excel is one of those, which is quite unknown. It can be very helpful though: For example it can easily convert the weekday-number into the weekday name. Often, it is used within other formulas. In this article you learn everything you need to know about the formula.

 

Structure of the CHOOSE formula

Structure of the CHOOSE formula.

Structure of the CHOOSE formula.

The CHOOSE formula returns a value from a list of values. So basically you provide a list of values (or references) and tell Excel, which one of these values to return.

The structure is quite straightforward (see the image above):

  1. The INDEX number determines, which item from the following list the formula has to return.
  2. You have at least to provide one option as the return value. This could be a fixed value or another cell reference.
  3. The CHOOSE formula would make much sense if you don’t provide more than one value to choose from. So number 3 in Figure 44 stands for the second value to choose from.
  4. You can add up to 254 values.




Example for the CHOOSE formula

Simple example for the CHOOSE formula.

Simple example for the CHOOSE formula.

The first example is very simple. You got the two data sets as shown in Figure 45. “BMW” costs 15,000 USD and Mercedes 20,000 USD.

In cell E2 you select which value to return, either the first one (price of BMW, 15,000 USD) or the second one (the price of Mercedes, 20,000 USD).

The first argument of the CHOOSE formula selects which value to return. In this case, it refers to cell E2 which says 2. The second part (“B3”) and the third part (“B4”) contain the list to choose from.

So, what will the formula in E3 return after pressing Enter on the keyboard? Correct, it’s 20,000. Why? Because the number 2 in cell E3 determines, that the second item from the list in the CHOOSE formula will be returned. The second item of the list is the cell reference to cell B4 so that the value 20,000 from cell B4 is returned.

 

Another example for the CHOOSE formula

Advanced example for the CHOOSE formula.

Advanced example for the CHOOSE formula.

After you’ve explored a simple example, you can now take a look at a more advanced one as pictured in Figure 46.

In cell F2 you want to type a year, for example 2017. In the cell below you want to get the sum of the prices of the selected year of the two cars shown on the left-hand side. So if you type 2017 in F2, you want to get the sum of 16,000 USD and 20,500 USD from cells C3 to C4.

That means, you basically need the SUM formula for adding up the values. But instead of providing a fixed range, the cell reference should switch between the two columns B and C.

The first part of the CHOOSE formula determines, which of the following cell references to use. You could simple insert the difference of 2018 and the value in F2. So if you write 2017 into cell F2, 2018 – 2016 is 1 so that CHOOSE returns the first item from the cell reference list. If you type 2016 on the other hand, the formula returns the second cell reference as 2018-2016=2. So you only have to give the correct cell references as the second and third part of the CHOOSE formula. The complete formula is:

=SUM(CHOOSE(2018-F2,C3:C4,B3:B4))

 


Hold on a second. Was this information helpful so far?

CHOOSE Formula in Excel: Everything You Should Know

If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:

newsletter The best Excel tips, tricks and tutorials.
newsletter 1x per month.
newsletter No spam. Promised.

CHOOSE Formula in Excel: Everything You Should Know

Your welcome gift: Our big 45 pages keyboard shortcuts package.

CHOOSE Formula in Excel: Everything You Should Know

Subscribe now!

Learn more


 

A popular example: The weekday name

The CHOOSE formula is probably most often used in context with the weekday name. You want to get the name of the weekday from a cell containing a date.

weekday, name, date, link, reference, excel, day

Get the weekday name from any date given in another cell.

If you want to get the weekday name of the date, given in another cell, please copy and paste this formula. Instead of “A1” within the WEEKDAY formula, just refer to the cell you like.

=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

 

Please refer to this article for more information about the weekday name.

Was the information helpful in this article?

  • If yes, why don't you subscribe to our free, monthly Excel newsletter?
  • If no, please let us know what to improve.




find, search, find(), search(), findb, searchb, formula, formulas

FIND & SEARCH in Excel: How and When to Use These Formulas

The two formulas FIND and SEARCH in Excel are very similar. They search through a cell or some text for a keyword or character. Once found, they return the number of characters, at which the keyword starts. Let’s learn how to use them and explore the differences of the two formulas.

[…]

formulas, dictionary, excel, language, languages

Dictionary for Excel Formulas

Microsoft Excel supports many languages. In total, you can use Excel in 107 different languages. Many of these languages also use local formula names. For example, VLOOKUP is called SVERWEIS in German, BUSCARV in Spanish or DÜŞEYARA in Turkish. Fortunately, you don’t need to remember different formula names because Excel automatically translates the formulas to your language. However, in some cases you need to know the local formula term. Please refer to our comprehensive dictionary below.

[…]

len, formula, length, excel

LEN Formula: Get the Number of Characters in Excel

You need to know how many characters does the cell contain?

indirect, formula, excel

INDIRECT: How to Use Text as a Cell Reference in Excel

You want to get data from different sheets but always on the same cell? INDIRECT returns the value of a cell which you specify by a string. For example, if you write =INDIRECT(“B2”) it’ll give you the value of B2. Instead of just B2, you can also refer to other sheets, for example =INDIRECT(“Sheet1!B2”) or even other workbooks.

weekday, day, name, monday, sunday

Weekday Name in Excel: Paste This Easy Formula into Excel Cell

You want to know the weekday of a certain date? Excel provides the WEEKDAY formula. The WEEKDAY formula returns the day of the week as a number, for example 1 for Sunday, 2 for Monday and so on. You can modify, which weekday starts with one.

countifs, excel, countif

COUNTIFS in Excel: Everything You Need to Know

The COUNTIFS function works similar like the SUMIFS function. But instead of adding up the values, it counts how many items with one or more criteria are in your table.

hlookup, excel, eagle, look, search, lookup, vlookup

HLOOKUP in Excel: Everything you need to know

You’ve probably heard of VLOOKUP which is a very popular and powerful formula in Excel. Far less known is the little brother: HLOOKUP. It basically works the same way as VLOOKUP with one difference: Instead of looking up values vertically, HLOOKUP works horizontally. In this article, you learn how to use HLOOKUP, what to keep in mind, possible error messages and some more advices about HLOOKUP.  […]

vlookup, hlookup, lookup, excel, how, guide

VLOOKUP in Excel: All You Need to Know About the Powerful Formula

One of the most popular formulas in Excel is the VLOOKUP. VLOOKUP is not as easy to use as a SUM or COUNT. In this article, you learn how to use VLOOKUP, what to keep in mind, possible error messages and some more advices about VLOOKUP.  […]

calculation, options, calculate, manual, automatic, excel, thumbnail

Calculation Options in Excel: Decide When and What to Calculate

Does this sound familiar to you: Excel takes too much time calculating. Instead of instantly showing the results, you have to wait for several seconds or even minutes for Excel to finish up the calculation. The problem: The larger your Excel model gets, the more you get frustrated by the lack of performance.

[…]