Excel Lookup Using Date Ranges and Multiple Criteria in Power Query

Some data analyses are best solved with a proper database and a simple SQL query. In my opinion, this is one of those examples. However, such conveniences are not always available.

In this scenario, we have a table containing the number of hours each person worked on a particular date. In a second table, we have the hourly rate that is valid for a particular period of time for each person. We want to calculate the total cost for each person on each date using the hours worked and the valid effective rate for that particular date.

While this example focuses on hourly rates, the concept is applicable to countless scenarios such as looking up prices for time-based sales or discounts, employee salaries, document revisions, or data that has a defined lifespan.

In the expected result, we find the rate varies based on the person and the date to calculate the correct amount.

The Data

As I described earlier, we have two data sets in Excel. The first data set includes the hours worked on a particular date by a particular person. I’ll refer to the first data set as the Hours table. The second data set includes the valid rate for a particular person over an effective period defined by a start and end date. I’ll refer to the second data set as the Rate table.

Method 1: Excel Array Formula

Before we get into the Power Query solution, I’ll provide a solution using an array formula (or CSE formula). This is certainly easier than adding the overhead and complexity needed for Power Query. However, I find the formula to be more difficult to read and maintain for the average Excel user. I described a similar solution in an earlier post titled Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula. If you decide to use this formula, make sure you press Ctrl + Shift + Enter to confirm the formula.

This formula assumes the Hours data set is on a worksheet named HOURS and the Rates data set is on a worksheet named RATES.

=INDEX(RATES!$D$2:$D$5,MATCH(1,IF(HOURS!$B2>=RATES!$B$2:$B$5,IF(HOURS!$B2<=RATES!$C$2:$C$5,IF(HOURS!$A2=RATES!$A$2:$A$5,1))),0))

The following is another version of the above array formula with a more simplified but potentially less readable structure.

=INDEX(RATES!$D$2:$D$5,MATCH(1,(HOURS!$B2>=RATES!$B$2:$B$5)*(HOURS!$B2<=RATES!$C$2:$C$5)*(HOURS!$A2=RATES!$A$2:$A$5),0))

Method 2: Power Query

This method uses Power Query to lookup the correct rate. The heart of this solution is a straightforward merge query with a date filter.

Method 2: Step 1 – Define Named Ranges

In this step, the Hours table and Rates table are given defined names. In Excel, on the Formulas ribbon click Name Manager. Click New… and create a new entry named TBL_HOURS as follows. Click OK when complete.

Now define a name for the Rates table. Click New… and create a new entry named TBL_RATES as follows. Click OK when complete. Click Close to exit the Name Manager.

Method 2: Step 2 – Get Data Into Power Query

On the Data ribbon in Excel, click Get Data and select From Other Sources and then Blank Query. The Power Query Editor opens. From the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties change the query name to PQ_TBL_HOURS. This code brings the data found in the range with defined name TBL_HOURS into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_HOURS"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"DATE", type date}, {"HOURS", type number}})
in
    #"Changed Type"

Next we’ll bring the TBL_RATES data into Power Query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_RATES. This code brings the data found in the range with defined name TBL_RATES into Power Query. This query should be set as a Connection Only query.

let
    Source = Excel.CurrentWorkbook(){[Name="TBL_RATES"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([NAME] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"NAME", type text}, {"EFFECTIVE START DATE", type date}, {"EFFECTIVE END DATE", type date}, {"RATE", Currency.Type}})
in
    #"Changed Type"

Method 2: Step 3 – Lookup Query

Now we create the lookup query. Remaining in the Power Query Editor, from the Home ribbon click Advanced Editor and add the following code. Click Done. In the Query Settings pane under Properties, change the query name to PQ_TBL_SPEND. Close the Power Query Editor and load PQ_TBL_SPEND to a Table in a New worksheet.

let
    Source = Table.NestedJoin(PQ_TBL_HOURS, {"NAME"}, PQ_TBL_RATES, {"NAME"}, "PQ_TBL_RATES", JoinKind.LeftOuter),
    #"Expanded PQ_TBL_RATES" = Table.ExpandTableColumn(Source, "PQ_TBL_RATES", {"NAME", "EFFECTIVE START DATE", "EFFECTIVE END DATE", "RATE"}, {"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE", "PQ_TBL_RATES.RATE"}),
    #"Filtered Rates" = Table.SelectRows(#"Expanded PQ_TBL_RATES", each [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rates",{{"PQ_TBL_RATES.RATE", "RATE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "AMOUNT", each [HOURS]*[RATE]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"PQ_TBL_RATES.NAME", "PQ_TBL_RATES.EFFECTIVE START DATE", "PQ_TBL_RATES.EFFECTIVE END DATE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"AMOUNT", Currency.Type}})
in
    #"Changed Type"

The first step in the query performs a Merge between PQ_TBL_HOURS and PQ_TBL_RATES based on the common NAME column. The matching PQ_TBL_RATES entries are added as a column of tables.

The second step expands the PQ_TBL_RATES column table. For each row in TBL_HOURS and TBL_RATES where the NAME column matched we find corresponding expanded rows. This creates entries where PQ_TBL_HOURS data is duplicated for each date based on the number of entries in PQ_TBL_RATES.

To eliminate the rows where the DATE column from TBL_HOURS does not exist between PQ_TBL_RATES.EFFECTIVE START DATE and PQ_TBL_RATES.EFFECTIVE END DATE we apply a filter using SelectRows and the condition [DATE] >= [PQ_TBL_RATES.EFFECTIVE START DATE] and [DATE] <= [PQ_TBL_RATES.EFFECTIVE END DATE]. We find the correct rates are now applied for the particular person and particular date.

Rename column PQ_TBL_RATES.RATE to RATE.

Add a new column to calculate AMOUNT which is defined as HOURS multiplied by RATE.

We no longer need the PQ_TBL_RATES.NAME, PQ_TBL_RATES.EFFECTIVE START DATE, and PQ_TBL_RATES.EFFECTIVE END DATE columns so they are removed.

The final step is to clean up the data types. AMOUNT is changed to the Currency data type.

Result

The calculated table is loaded to a table on a new worksheet. We see the correct effective rates are applied and the resulting amount is correct.