Excel =LOOKUP() with conditions

This entry is part 5 of 7 in the series The gas mon­ey split­ting tool series

The Excel =LOOKUP() func­tion is one of the ways to get a val­ue from a row/column that sits against an exact or approx­i­mate match in anoth­er. This way you search through the Excel lookup range for a par­tic­u­lar val­ue and depend­ing on it being matched pre­cise­ly or close­ly anoth­er val­ue is returned from the results range.

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 vehicle.

In the next step, we go Scrooge mode* by try­ing to pin-point the gas pric­ing that was applic­a­ble for the fuel in the tank. For this pur­pose, data will be obtained from a range with the =LOOKUP() func­tion. How­ev­er, it will be more com­pli­cat­ed as a the con­di­tion to skip blanks will be applied.

*Such cheap­skate behav­iour is not social­ly encour­aged but it fits well into the nar­ra­tive of the file being con­struct­ed. Hence, we are going along with it.


The attached file is used for the pur­pos­es of this post:


Here for the conditions only?

In case you already know how =LOOKUP() works and only care to know how to apply con­di­tions, you can jump straight to that part of the post.

Data to be used

To start off sim­ply we assume that the vehi­cle used for trav­el runs on petrol and we have some his­to­ry of gas prices:

Gasoline prices history.

This way if an arbi­trary trip date is select­ed, the most recent fuel cost will be pop­u­lat­ed in the calculator:

Populating the gas money calculator with the fuel price for a trip date.

The notable thing about the petrol prices table is that, nat­u­ral­ly, dates are sort­ed chrono­log­i­cal­ly. This is impor­tant as they will rep­re­sent the search vec­tor in which the trip date is looked-up. The =LOOKUP() func­tion requires that vec­tor be sort­ed in ascend­ing order and not doing so bears a huge risk of wrong results.

Two named ranges are added to demon­strate the basic work­ing. They won’t be need­ed lat­er on so in future posts from the series they will be removed. How­ev­er, you will be able to see them in the attached spreadsheet.

Gasoline prices history - named ranges.
Excel lookup — simple form

In the exam­ple, 14.02.2020 is select­ed as a trip date. There is no refu­elling on the same day but plen­ty of infor­ma­tion on ear­li­er peri­ods. Look­ing up the applic­a­ble price, in this case of 2,19/L as of 11.02.2020, goes like this:

Excel lookup simple form.
=LOOKUP(B6;Prices_Dates;Prices_Values)

The 3 argu­ments are:

  • The lookup val­ue — the trip date;
  • The search vec­tor — the refu­elling dates;
  • The result vec­tor — the fuel prices.

One thing I like about the =LOOKUP() is that the two vec­tors are sep­a­rate ranges that can be moved around. This way if lat­er on new infor­ma­tion should be added, the for­mu­la would not need any changes:

The =LOOKUP() itself will loop through the dates in the pric­ing table and try to find the one searched for by the user. If it present more than once, the result for the lat­est instance will be tak­en. Nev­er­the­less, if no exact match is present the result for the first ele­ment that is small­er (ear­li­er) than the user choice is derived. In the exam­ple above the 14.02.2020 date is nowhere to be found so the func­tion returns the val­ue of 2,19 for 11.02.2020 — the first date on record that is before the journey.

Complicating the data

In the cal­cu­la­tor, there were logged vehi­cles run­ning on petrol and diesel. Hence, it makes sense for the pric­ing fig­ures to include both fuels. Often such infor­ma­tion would be struc­tured as shown below:

This cre­ates 2 problems:

  1. Depend­ing on the date, there is a chance that a price for par­tic­u­lar fuel would not have been record­ed. In the case of petrol, if the same for­mu­la as before was used, it would have returned 0 — the last date on record­ed before the trip date (14.02.2020) is 13.02.2020. How­ev­er, only the diesel price is avail­able and Excel is not smart enough to make the dif­fer­ence with­out some help.
  2. The for­mu­la would need to address the right set of data.

For now, the first issue would be tack­led while the oth­er one would wait for the next post.

Adding condition to the =LOOKUP()

The petrol prices would remain the focus. Due to the rea­sons point­ed above, we need to fil­ter out the lines that have blanks. This will be done on the fly by employ­ing an array for­mu­la:

{=LOOKUP(B6;IF(Prices_Values<>””;Prices_Dates);Prices_Values)}

The =LOOKUP() func­tion has only one (impor­tant) change — instead of address­ing the refu­elling dates as they are, we first check whether the cor­re­spond­ing val­ues for the petrol prices are not blank. As a result, Excel will be forced to replace the dates with FALSE on all occa­sions when the con­di­tion is not met.

Click to see how it looks like.
Ref. DatePetrolHas val­ueRef. date (LOOKUP)
1.12.20192,24Yes1.12.2019
3.1.2020NoFALSE
5.1.2020NoFALSE
18.1.20202,27Yes18.1.2020
11.2.20202,19Yes11.2.2020
13.2.2020NoFALSE
24.2.20202,26Yes24.2.2020
5.3.20202,13Yes5.3.2020
9.3.2020NoFALSE
20.3.20202,14Yes20.3.2020
The FALSE val­ues will not be treat­ed as numbers/dates by the =LOOKUP()

*As with all array for­mu­las — the com­bi­na­tion of [Ctrl]+[Shift]+[Enter] must be used.

Notes

One aspect not addressed in the for­mu­la is how to han­dle the error that will emerge if the ear­li­est ele­ment is after the date we are look­ing-up prices for. Error pre­ven­tion is impor­tant and can take many forms. Two pos­si­ble solu­tions are:

  • To con­trol the trip date input via data val­i­da­tion so it is no ear­li­er than the first pric­ing record.
  • To assume the ear­li­est record’s pric­ing for all instances when the jour­ney was made before it.
Series Nav­i­ga­tion« Find­ing data in Excel with INDEX()+MATCH()=CHOOSE() from a list of val­ues in Excel »