The Excel =LOOKUP() function is one of the ways to get a value from a row/column that sits against an exact or approximate match in another. This way you search through the Excel lookup range for a particular value and depending on it being matched precisely or closely another value is returned from the results range.
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 next step, we go Scrooge mode* by trying to pin-point the gas pricing that was applicable for the fuel in the tank. For this purpose, data will be obtained from a range with the =LOOKUP() function. However, it will be more complicated as a the condition to skip blanks will be applied.
*Such cheapskate behaviour is not socially encouraged but it fits well into the narrative of the file being constructed. Hence, we are going along with it.
The attached file is used for the purposes of this post:
Here for the conditions only?
In case you already know how =LOOKUP() works and only care to know how to apply conditions, you can jump straight to that part of the post.
Data to be used
To start off simply we assume that the vehicle used for travel runs on petrol and we have some history of gas prices:
This way if an arbitrary trip date is selected, the most recent fuel cost will be populated in the calculator:
The notable thing about the petrol prices table is that, naturally, dates are sorted chronologically. This is important as they will represent the search vector in which the trip date is looked-up. The =LOOKUP() function requires that vector be sorted in ascending order and not doing so bears a huge risk of wrong results.
Two named ranges are added to demonstrate the basic working. They won’t be needed later on so in future posts from the series they will be removed. However, you will be able to see them in the attached spreadsheet.
Excel lookup — simple form
In the example, 14.02.2020 is selected as a trip date. There is no refuelling on the same day but plenty of information on earlier periods. Looking up the applicable price, in this case of 2,19/L as of 11.02.2020, goes like this:
The 3 arguments are:
- The lookup value — the trip date;
- The search vector — the refuelling dates;
- The result vector — the fuel prices.
One thing I like about the =LOOKUP() is that the two vectors are separate ranges that can be moved around. This way if later on new information should be added, the formula would not need any changes:
The =LOOKUP() itself will loop through the dates in the pricing table and try to find the one searched for by the user. If it present more than once, the result for the latest instance will be taken. Nevertheless, if no exact match is present the result for the first element that is smaller (earlier) than the user choice is derived. In the example above the 14.02.2020 date is nowhere to be found so the function returns the value of 2,19 for 11.02.2020 — the first date on record that is before the journey.
Complicating the data
In the calculator, there were logged vehicles running on petrol and diesel. Hence, it makes sense for the pricing figures to include both fuels. Often such information would be structured as shown below:
This creates 2 problems:
- Depending on the date, there is a chance that a price for particular fuel would not have been recorded. In the case of petrol, if the same formula as before was used, it would have returned 0 — the last date on recorded before the trip date (14.02.2020) is 13.02.2020. However, only the diesel price is available and Excel is not smart enough to make the difference without some help.
- The formula would need to address the right set of data.
For now, the first issue would be tackled while the other one would wait for the next post.
Adding condition to the =LOOKUP()
The petrol prices would remain the focus. Due to the reasons pointed above, we need to filter out the lines that have blanks. This will be done on the fly by employing an array formula:
The =LOOKUP() function has only one (important) change — instead of addressing the refuelling dates as they are, we first check whether the corresponding values for the petrol prices are not blank. As a result, Excel will be forced to replace the dates with FALSE on all occasions when the condition is not met.Click to see how it looks like.
|Ref. Date||Petrol||Has value||Ref. date (LOOKUP)|
*As with all array formulas — the combination of [Ctrl]+[Shift]+[Enter] must be used.
One aspect not addressed in the formula is how to handle the error that will emerge if the earliest element is after the date we are looking-up prices for. Error prevention is important and can take many forms. Two possible solutions are:
- To control the trip date input via data validation so it is no earlier than the first pricing record.
- To assume the earliest record’s pricing for all instances when the journey was made before it.