The =CHOOSE() function in Excel is, to my surprise, rather unappreciated. It returns a value laying at a chosen position (index) in a list the user has predefined. The entries in the list can be both values and cell references.
Need to know the very basic use of =CHOOSE() with no other context?
You can go down to a very simple example with file attached.
The Gas Money Splitting Tool Series
In the current series, a gas money splitting tool is made. Before it was demonstrated how to filter out unique items to set the start/endpoint of a trip; and how to INDEX/MATCH the technical and consumption parameters of the vehicle. In the previous post, the =LOOKUP() function was employed to give the petrol price for the latest date with data. Moreover, in this post, we will instruct Excel to choose between the pricing of petrol and diesel based on the vehicle used in the journey.
The file below is the outcome of the post. Use it as you follow along:
Data & Ranges
Just like before — some named ranges that will make the formulas more readable. Find them on the Prices:
Looking up the prices of petrol only
As different fuels have been pumped on different dates, there is the problem of finding the row with actual entry. When demonstrating the conditional =LOOKUP() this was done for Petrol only. The array formula was:
The arrows indicate the reference to the petrol prices. Starting from this position we can make a small tweak and feed the appropriate range of cells in case a diesel car was driven.
Choosing the appropriate column
We can write the following formula:
and it will return the range of petrol cost. Had the first argument been 2, it was going to result in the set of diesel prices.
Further, finding the value for the first argument can be done with =MATCH():
This, of course, will look at the column headers (Prices_Fuel_Types) and find an (exact) match for diesel in the second column’s name. Replacing the manually input 1 in the =CHOOSE() function above results in formula that can match the car fuel type with its pricing history:
*Short note here — If input in an array this formula will work and will replicate the range found. This can be useful as well but for the current task — it is irrelevant.
Going back to the =LOOKUP()
Now we can use the resulting formula and supplement the range in the original =LOOKUP() that was marked with red arrows:
By doing so the conditional =LOOKUP() is now updated to search in the column with the matching fuel-type prices.
This could be done with =IF()
Certainly employing =IF() would work. However, there are 2 reasons why I didn’t choose to go with it:
- Clarity — First, in the current case we see only two options to choose from. Hence, the list in the =CHOOSE() function was short. However, if few more options for fuel were present (eg. Gas, Ethanol) nesting =IF()s would have made it less convenient to read and work with the formula.
- Second, I wanted to mention the =CHOOSE() function on the blog and I decided to fit it the series story.
The obvious shortcoming of this solution is that the list is hard-coded in specific order. This can cause spectacular mess if reference is made to range of cells (as in the example) if their placement around the sheet is changed but at the same time the method to derive the desired index (first argument) is not accounting for the change.
=CHOOSE() — simple form
The simplest form of the =CHOOSE() function can be used to pick the short name for a month. I have seen many people labelling their monthly reports with short months’ names so to refer to that data I need the 3‑letter name (eg. Jan, Feb). However, I tend to use end of month dates for column names. One way to address this mismatch is to take the month number as the first argument of the =CHOOSE() and then input 12 more that spell out the monthly names. Therefore, if I have:
the first argument of 3 will make Excel choose the third of the other 12 elements and the formula will return Mar.
Furthermore, the first argument can be then made dependent on report date or other field and change dynamically.
Example attached below: