Need to retrieve matching Date, Type, ID, and reference Cost based on user input Date Range. In Data Set 1 and Data Set 2 inputs are not sorted, and the match conditions need to look at if it falls within the Date Range first, check if the Type matches second, and if the IDs match third. If all (3) of these conditions pass it will list them in the Desired Output and pull the associated Cost. If the Cost occur on the same date, it will add them together. Prefer an Excel 2019 formula-based approach over VBA.

Need to retrieve matching Date, Type, ID, and reference Cost based on user input Date Range. In Data Set 1 and Data Set 2 inputs are not sorted, and the match conditions need to look at if it falls within the Date Range first, check if the Type matches second, and if the IDs match third. If all (3) of these conditions pass it will list them in the Desired Output and pull the associated Cost. If the Cost occur on the same date, it will add them together. Prefer an Excel 2019 formula-based approach over VBA.

• arrays
• excel
• indexing
• match
• aggregate

• 1 Since Excel 2019 does not have FILTER, you can use SUMIFS combined with INDEX and MATCH to get results. – Emad Kerhily Commented Mar 6 at 22:39
• 1 Use a Pivot Table instead – Michal Commented Mar 6 at 22:53
• How would you solve it using FILTER? I might just buy excel 2021 just for that feature. – mjac Commented Mar 7 at 21:19

2 Answers 2

This can also be accomplished using Power Query.

The downside is that you will have to refresh after making any changes in the Datasets.
This could be automated using an event-triggered macro.

To use Power Query

• Select the table area (first 8 columns) containing only the Data sets and
  Home => Format as Table
  • Change the Table Name to Fruits.
• Select the Date Range dates and Home => Format as Table.
  • Change that table name to StartEnd
• Select some sell in the Fruits table.
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Paste the M Code below in place of what you see
• Read the comments and explore the Applied Steps to understand the algorithm

let

//Change Lines 1 and 3 below to reflect actual table names
    #"User Input" = Excel.CurrentWorkbook(){[Name="StartEnd"]}[Content],
    #"Start/End" = Table.TransformColumnTypes(#"User Input",{{"Start", type date},{"End", type date}}),
    Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Type", type text}, {"ID", Int64.Type}, {"Cost", Currency.Type}, 
        {"Date2", type date}, {"Type3", type text}, {"ID4", Int64.Type}, {"Cost5", Currency.Type}}),

//Split and filter the data sets
    #"Split Data Sets" = 
        List.Accumulate(
             {0,1},
             {},
             (s,c)=>s & {
                 Table.SelectRows(
                    Table.RenameColumns(
                        Table.SelectColumns(#"Changed Type",
                            List.Range(Table.ColumnNames(#"Changed Type"),c*4,4)),
                        List.Zip(
                            {List.Range(Table.ColumnNames(#"Changed Type"),c*4,4), 
                            List.FirstN(Table.ColumnNames(#"Changed Type"),4)})),
                each [Date]>=#"Start/End"[Start]{0} and [Date]<=#"Start/End"[End]{0})
                    }),

//Join Tables based on Type and ID
    #"Join Table" = Table.NestedJoin(#"Split Data Sets"{0},{"Type","ID"}, #"Split Data Sets"{1},{"Type","ID"},"Join",JoinKind.Inner),

//Stack the tables
    #"Stack Tables" = Table.Combine({Table.RemoveColumns(#"Join Table",{"Join"})} & #"Join Table"[Join]),
    #"Grouped Rows" = Table.Group(#"Stack Tables", {"Date", "Type", "ID"}, {{"Cost", each List.Sum([Cost]), Currency.Type}}),

//Sort by Date
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

If your output section has for example:

Time Range Start: M3

Time Range End: N3

Type: O4

ID: P4

Use this formula to sum the costs from both sheets based on conditions:

=SUMIFS(F:F, A:A, ">="&$M$3, A:A, "<="&$N$3, D:D, O4, E:E, P4) +

SUMIFS(K:K, H:H, ">="&$M$3, H:H, "<="&$N$3, I:I, O4, J:J, P4)

The first SUMIFS checks Sheet1 (A:A for Date, D:D for Type, E:E for ID, F:F for Cost).

The second SUMIFS checks Sheet2 (H:H for Date, I:I for Type, J:J for ID, K:K for Cost).

Assuming the first output row for Date is in cell Q4, use:

=IFERROR(INDEX(A:A, MATCH(1, (A:A >= $M$3) * (A:A <= $N$3) * (D:D = O4) * (E:E = P4), 0)), "")