Excel Match Index multiple criteria and multiple dates

Question

=INDEX(D1:D6;MATCH(1;(E1=A1:A6)*(E2=B2:B6)*(DATEVALUE(MID(E3;1;10))>=DATEVALUE(MID(C1:C6;1;10)));0))

I have an excel file that has prices per productId combination ( columns A and B ) and by effective dates. As you can see it may contain duplicates for the productId combination and they are valid only after the date has passed. This is the formula i came up with till now but it only works if the rows are sorted by A B and C ( especially C should be sorted descending). In the example i am expecting the correct result price to be 600.00 but it returns 200.00 .

I understand why this happens. But i don't know how to fix it and make it work correctly. Correctly means :

Give the product ids: D318JV 00B6

if effectiveDate >= 07/18/2019 00:00:00 => 600.00

if effectiveDate between 07/07/2019 00:00:00 AND 07/18/2019 => 400.00

if effectiveDate between 06/01/2019 00:00:00 AND 07/07/2019 => 200.00

if effectiveDate before 06/01/2019 00:00:00 => N/A

Any help to make this formula work will be appreciated

Answer 1

Use the following ARRAY formula using 1 Formula only:

=INDEX(D1:D6,MATCH(1,(E1=A1:A6)*(E2=B1:B6)*(C1:C6=MAX(IF(C1:C6<E3,C1:C6,0))),0))

Make sure you confirm the formula with CTRL+SHIFT+ENTER. See screenshot of example working:

You may have to change the , for ;. If that's the case the formula will look like:

=INDEX(D1:D6;MATCH(1;(E1=A1:A6)*(E2=B1:B6)*(C1:C6=MAX(IF(C1:C6<E3;C1:C6,0)));0))

Answer 2

Combining the approaches from the question and from comments to @matro's answer:

0. get the exact date (highest one meeting the criteria), using an array formula in cell E4
   =MAX(IF((E1=A1:A6)*(E2=B1:B6)*(E3>=C1:C6); C1:C6))
   (confirmed by Ctrl+Alt+Enter instead of just Enter)
1. modify the original formula to
   =INDEX(D1:D6; MATCH(1; (E1=A1:A6)*(E2=B1:B6)*(E4=C1:C6); 0))

Answer 3

DATA: Table with the following fields: Product, ID, Effective Date, Price

Requirements: Extract the Price of a Product Id that corresponds to a given date range.

Assumptions: This solution assumes that the Product, Id, Price and Date combinations are unique.

Date Criteria: The following table shows the several criteria formats that could be used to retrieve the Price of a Product Id based on an Effective Date, it also shows the selection to be applied for each criterion when the result of the criteria includes several items.

Formulas: As the table contains unique combinations of Product, Id, Price and Date, and the expected output is numeric (i.e. Price), the use of the SUMIFS function seems appropriate.

Criteria: 1.Equals
Formula:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID, 
[Effective Date range], #Date )  

Where:
[Price range] = The Price column
[Product range] = The Product column
#Product = The Product we need to find
[ID range] = The ID column
#ID = The ID we need to find
[Effective Date range] = The Effective Date column
#Date = The Date we need to find

Other Criteria: The next five criteria might return several possible Dates, therefore we’ll use the function AGGREGATE to select the required Date as per the Criteria Table.
Formula:

= AGGREGATE( #Sel, 6, [Effective Date range] /
( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] #ƒ #Date) ), 1 )

Where:
#Sel = The selection to be applied as per the Criteria Table (i.e. 14 = Latest Date and 15 = Earliest Date)
#ƒ = The comparison to be applied base on the criteria required

Then we'll use the date resulting from the AGGREGATE function, in the SUMIFS function to obtain the required Price:
Formula:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID,
[Effective Date range], #AGGREGATE )

Where:
#AGGREGATE = The resulting date from the AGGREGATE function

For the last five criteria the base formula will be:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID, [Effective Date range],
AGGREGATE( #Sel, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID)
* ( [Effective Date range] #ƒ #Date ) ), 1 ) )

and we just replace #Sel and #ƒ as follows:

Criteria: 2.Before
#Sel = 14
#ƒ = <

Criteria: 3.Before or Equal
#Sel = 14
#ƒ = <=

Criteria: 4.Between
#Sel = 15
#ƒ = >= and <=
#Date1 = Date from
#Date2 = Date to
Formula:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID, [Effective Date range],
AGGREGATE( 15, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID)
* ([Effective Date range] >= #Date1) * ([Effective Date range] <= #Date2) ), 1 ) )

Criteria: 5. After or Equal
#Sel = 15
#ƒ = >=

Criteria: 6.After
#Sel = 15
#ƒ = >

Solution: In order to apply the required formula for each criteria we need to combine all of them into one formula. Although that could be achieved with a series of nested IFs, instead we’ll use the function CHOOSE

Formula:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID, [Effective Date range],
CHOOSE( #Criteria, #Date,
AGGREGATE( 14, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] < #Date) ), 1 ),
AGGREGATE( 14, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] <= #Date) ), 1 ),
AGGREGATE( 15, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] >= #Date1) * ([Effective Date range] <= #Date2) ), 1 ),
AGGREGATE( 15, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] >= #Date) ), 1 ),
AGGREGATE( 15, 6, [Effective Date range] / ( ([Product range] = #Product) * ([ID range] = #ID) 
* ([Effective Date range] > #Date) ), 1 ) ) )

Where:
#Criteria = Criteria applied as per the Criteria Table (i.e. 1 to 6)
#ƒ = The comparison to be applied base on the criteria required

A formula array could be used to reduce further the length of the formula
Formula Array:

= SUMIFS( [Price range], [Product range], #Product, [ID range], #ID, [Effective Date range],
IF( #Criteria = 1, #Date,
AGGREGATE( LOOKUP( #Criteria, {1,"N/A";2,14;4,15} ), 6, [Effective Date range] /
( ( [Product range] = #Product ) * ( [ID range] = #ID )
* CHOOSE( #CRITERIA, "N/A",
[Effective Date range] < #Date, [Effective Date range] <= #Date,
([Effective Date range] >= #Date1) * ([Effective Date range] <= #Date2),
[Effective Date range] >= #Date,[Effective Date range] > #Date ) ), 1 ) ) )

Implementation: Assuming the data table located at B2:E8

Enter this formula in cell L6 and copy to L7:L9

= SUMIFS( $E$6:$E$11, $B$6:$B$11, $H$6, $C$6:$C$11, $H$7, $D$6:$D$11,
CHOOSE( LEFT( $I6 ), $J6,
AGGREGATE( 14, 6, $D$6:$D$11 / ( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 ) 
* ( $D$6:$D$11 < $J6 ) ), 1 ),
AGGREGATE( 14, 6, $D$6:$D$11 / ( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 )
* ( $D$6:$D$11 <= $J6 ) ), 1 ),
AGGREGATE( 15, 6, $D$6:$D$11 / ( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 )
* ( $D$6:$D$11 >= $J6 ) * ( $D$6:$D$11 <= $K6 ) ), 1 ),
AGGREGATE( 15, 6, $D$6:$D$11 / ( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 )
* ( $D$6:$D$11 >= $J6 ) ), 1 ),
AGGREGATE( 15, 6, $D$6:$D$11 / ( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 )
* ( $D$6:$D$11 > $J6 ) ), 1 ) ) )

Enter this formula array in cell M6 and copy to M7:M9

FormulaArrays are entered pressing CTRL + SHIFT + ENTER simultaneously, the formula would be wrapped within { and } if entered correctly.

= SUMIFS( $E$6:$E$11, $B$6:$B$11, $H$6, $C$6:$C$11, $H$7, $D$6:$D$11,
IF( --LEFT( $I6 ) = 1, $J6,
AGGREGATE( LOOKUP( --LEFT( $I6 ), {1,"N/A";2,14;4,15} ), 6, $D$6:$D$11 /
( ( $B$6:$B$11 = $H$6 ) * ( $C$6:$C$11 = $H$7 )
* CHOOSE( LEFT( $I6 ), "N/A",
$D$6:$D$11 < $J6, $D$6:$D$11 <= $J6,
( $D$6:$D$11 >= $J6 ) * ( $D$6:$D$11 <= $K6 ),
$D$6:$D$11 >= $J6, $D$6:$D$11 > $J6 ) ), 1 ) ) )

An alternate format is seen in the figure below:

Suggested reading about the resources used:
AGGREGATE function, CHOOSE function, SUMIFS function

Answer 4

I believe @Kaiser has already provided the right answer, but if he can incorporate DATEVALUE into his function it will be even better. It seems that the original dates in column C (and possibly the look up date as well) are text.

So the final formula may look something like this:

{=INDEX(D1:D6,MATCH(1,(A1:A6=E1)*(B1:B6=E2)*(DATEVALUE(C1:C6)=MAX(IF(DATEVALUE(C1:C6)<DATEVALUE(E3),DATEVALUE(C1:C6),0))),0))}

Or like this in @isioutis' system:

{=INDEX(D1:D6;MATCH(1;(A1:A6=E1)*(B1:B6=E2)*(DATEVALUE(C1:C6)=MAX(IF(DATEVALUE(C1:C6)<DATEVALUE(E3);DATEVALUE(C1:C6);0)));0))}

My habit is to give name(s) to range(s) to make my formula easier to be interpreted, so if you do the following:

Name Column A as 'ProductId_A'

Name Column B as 'ProductId_B'

Name Column C as 'Date_List'

Name Column D as 'Price_List'

and name the look up criteria as

'Lookup_A'

'Lookup_B'

'Lookup_Date'

you will have a formula looks like this instead:

{=INDEX(Price_List,MATCH(1,(ProductId_A=Lookup_A)*(ProductId_B=Lookup_B)*(DATEVALUE(Date_List)=MAX(IF(DATEVALUE(Date_List)<DATEVALUE(Lookup_Date),DATEVALUE(Date_List),0))),0))}

or

{=INDEX(Price_List;MATCH(1;(ProductId_A=Lookup_A)*(ProductId_B=Lookup_B)*(DATEVALUE(Date_List)=MAX(IF(DATEVALUE(Date_List)<DATEVALUE(Lookup_Date);DATEVALUE(Date_List);0)));0))}

Please see below some test results (and note that the date format in my system is dd/mm/yyyy):

One thing to note is that if the following statement by isioutis is valid,

they are valid only after the date has passed

then the looked up price on 07/18/2019 00:00:00 should be $400 as technically the day has not yet passed which means $600 is not the valid price for that date. Otherwise please use "<=" (less than and equal to) in the formula when comparing the look up date with the date list.

Cheers :)

Answer 5

If I understood your question correctly, you would like to find relevant price (= output variable) for a given product at a given time (= input variables).

I tried to reconstruct your use case using only a simple productID (1 and 2) together with effective date and price as stated in your question. I think I found the solution and used this site to get to it.

In general, the solution might be array formula with combination of MAX and IF function. A little bit of analysis of your requirement was also needed as follows:

1. We want to find exact productID - first condition in if statement
2. We want to find the latest (or max) price applied at a given date - second condition in if statement (the effective date has to be less or equal to the given date) together with MAX function applied on the price column.

Please let me know, if my explanation together with the source page is not clear enough.

If that would be the case, here is my sample Excel file with proposed solution.

Thanks and regards.

Matej