=CHOOSE() from a list of values in Excel

The =CHOOSE() func­tion in Excel is, to my sur­prise, rather unap­pre­ci­at­ed. It returns a val­ue lay­ing at a cho­sen posi­tion (index) in a list the user has pre­de­fined. The entries in the list can be both val­ues and cell references.

Need to know the very basic use of =CHOOSE() with no other context?

You can go down to a very sim­ple exam­ple with file attached.

The Gas Money Splitting Tool Series

In the cur­rent series, a gas mon­ey split­ting tool is made. Before it was demon­strat­ed how to fil­ter out unique items to set the start/endpoint of a trip; and how to INDEX/MATCH the tech­ni­cal and con­sump­tion para­me­ters of the vehi­cle. In the pre­vi­ous post, the =LOOKUP() func­tion was employed to give the petrol price for the lat­est date with data. More­over, in this post, we will instruct Excel to choose between the pric­ing of petrol and diesel based on the vehi­cle used in the journey.

The file below is the out­come of the post. Use it as you fol­low along:

Data & Ranges

Just like before — some named ranges that will make the for­mu­las more read­able. Find them on the Prices:

Looking up the prices of petrol only

As dif­fer­ent fuels have been pumped on dif­fer­ent dates, there is the prob­lem of find­ing the row with actu­al entry. When demon­strat­ing the con­di­tion­al =LOOKUP() this was done for Petrol only. The array for­mu­la was:

The arrows indi­cate the ref­er­ence to the petrol prices. Start­ing from this posi­tion we can make a small tweak and feed the appro­pri­ate range of cells in case a diesel car was driven.

Choosing the appropriate column

We can write the fol­low­ing formula:

=CHOOSE(1;Prices_Values_Petrol;Prices_Values_Diesel)

and it will return the range of petrol cost. Had the first argu­ment been 2, it was going to result in the set of diesel prices.

Fur­ther, find­ing the val­ue for the first argu­ment can be done with =MATCH():

This, of course, will look at the col­umn head­ers (Prices_Fuel_Types) and find an (exact) match for diesel in the sec­ond colum­n’s name. Replac­ing the man­u­al­ly input 1 in the =CHOOSE() func­tion above results in for­mu­la that can match the car fuel type with its pric­ing history:

=CHOOSE(MATCH(B10;Prices_Fuel_Types;0);Prices_Values_Petrol;Prices_Values_Diesel)

*Short note here — If input in an array this for­mu­la will work and will repli­cate the range found. This can be use­ful as well but for the cur­rent task — it is irrelevant.

Going back to the =LOOKUP()

Now we can use the result­ing for­mu­la and sup­ple­ment the range in the orig­i­nal =LOOKUP() that was marked with red arrows:

By doing so the con­di­tion­al =LOOKUP() is now updat­ed to search in the col­umn with the match­ing fuel-type prices.

This could be done with =IF()

Cer­tain­ly employ­ing =IF() would work. How­ev­er, there are 2 rea­sons why I did­n’t choose to go with it:

• Clar­i­ty — First, in the cur­rent case we see only two options to choose from. Hence, the list in the =CHOOSE() func­tion was short. How­ev­er, if few more options for fuel were present (eg. Gas, Ethanol) nest­ing =IF()s would have made it less con­ve­nient to read and work with the formula.
• Sec­ond, I want­ed to men­tion the =CHOOSE() func­tion on the blog and I decid­ed to fit it the series story.

Downside

The obvi­ous short­com­ing of this solu­tion is that the list is hard-cod­ed in spe­cif­ic order. This can cause spec­tac­u­lar mess if ref­er­ence is made to range of cells (as in the exam­ple) if their place­ment around the sheet is changed but at the same time the method to derive the desired index (first argu­ment) is not account­ing for the change.

=CHOOSE() — simple form

The sim­plest form of the =CHOOSE() func­tion can be used to pick the short name for a month. I have seen many peo­ple labelling their month­ly reports with short months’ names so to refer to that data I need the 3‑letter name (eg. Jan, Feb). How­ev­er, I tend to use end of month dates for col­umn names. One way to address this mis­match is to take the month num­ber as the first argu­ment of the =CHOOSE() and then input 12 more that spell out the month­ly names. There­fore, if I have:

=CHOOSE(3;“Jan”;“Feb”;“Mar”;“Apr”;“May”;“Jun”;“Jul”;“Aug”;“Sep”;“Oct”;“Nov”;“Dec”)

the first argu­ment of 3 will make Excel choose the third of the oth­er 12 ele­ments and the for­mu­la will return Mar.

Fur­ther­more, the first argu­ment can be then made depen­dent on report date or oth­er field and change dynamically.

Exam­ple attached below: