INDEX,MATCH with Greater Than Date Criteria

[coloradobstime]

Good Morning! I found this thread here (http://www.mrexcel.com/forum/excel-questions/785753-index-match-greater-than-date-criteria.html) which is closely related to my issue but cannot figure out how to remove the "third criteria" from the formula in order to make it work for my problem. I utilize a program that uses "Dated Constant" variables which means it will utilize the value of that variable until the next change. I need to take similar logic to this in a spreadsheet I have for post-analysis. I will ensure the data set I am referencing has the appropriate values prior to the period I am looking at. As an example, the Folly1 unit minimum will be 120 past 01/01/2016 until the next change that occurs on 04/01/2016 which will move it to 105 until the next change.... so between January 1, 2016 and March 31, 2016 at 11:59:59 it remains 120 at 04/01/2016 12:00am it changes to 105.

Spreadsheet:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data					Lookups			Expected return value:
2	Date	Unit	Minimum Capacity			Date	Unit	Quantity	Minimum Capacity for Period Only
3	1/1/2016	Cat	0			1/1/2016	Folly1	202	120
4	1/1/2016	Dog1	3			1/2/2016	Folly1	700	120
5	1/1/2016	Dog2	3			4/1/2016	Folly1	1201	105
6	1/1/2016	Dog3	3			4/2/2016	Folly1	1201	105
7	1/1/2016	Elephant 5	28
8	1/1/2016	Elephant 6	45
9	1/1/2016	Elephant 6s	45			*Data will be sorted by Unit and then by Date
10	1/1/2016	Elephant 7	80
11	1/1/2016	Elephant 7s	80			One match type is an exact match column b to column g.
12	1/1/2016	Folly1	120
13	4/1/2016	Folly1	105			Second match type is 1 - Less than (largest value less than or equal to) column a to column f
14	10/1/2016	Folly1	120
15	4/1/2017	Folly1	118
16	10/1/2017	Folly1	120		Expected to: Find unit in data (column b) that matches unit in lookups (column g), then find the latest date (column a) less than or equal to date listed in lookups (column f) and return minimum capacity (column c) to column I.
17	4/1/2018	Folly1	105
18	10/1/2018	Folly1	120		Have tried:
19	4/1/2019	Folly1	105			Works for exact date match:
20	10/1/2019	Folly1	120			1/1/2016	Folly1	202	120
21	4/1/2020	Folly1	105			4/1/2016	Folly1	121	105
22	10/1/2020	Folly1	120			4/1/2017	Folly1	700	118
23	4/1/2021	Folly1	105
24	10/1/2021	Folly1	120			Does not work for between two dates….
25	1/1/2016	Folly2	200			1/2/2016	Folly1	202	#N/A
26
27						Reason: the match type is defined as type "0" for both…
28						=INDEX($A$1:$C$25,MATCH(1,($B$1:$B$25=G25)*($A$1:$A$25=F25),0),3)
29
30						Seen this post:
31						http://www.mrexcel.com/forum/excel-questions/785753-index-match-greater-than-date-criteria.html
32						but cannot figure out how to remove third criteria…

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

I am trying to:
Expected to: Find unit in data (column b) that matches unit in lookups (column g), then find the latest date (column a) less than or equal to date listed in lookups (column f) and return minimum capacity (column c) to column I.

Thanks for pointing me in the right direction!

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Data	*	*	*	*	Lookups	*	*	Expected return value:
2	Date	Unit	Minimum Capacity	*	*	Date	Unit	Quantity	Minimum Capacity for Period Only
3	1/1/2016	Cat	0	*	*	1/1/2016	Folly1	120	120
4	1/1/2016	Dog1	3	*	*	1/2/2016	Folly1	120	120
5	1/1/2016	Dog2	3	*	*	4/1/2016	Folly1	105	105
6	1/1/2016	Dog3	3	*	*	4/2/2016	Folly1	105	105
7	1/1/2016	Elephant 5	28	*	*	*	*	*	*
8	1/1/2016	Elephant 6	45	*	*	*	*	*	*
9	1/1/2016	Elephant 6s	45	*	*	*	*	*	*
10	1/1/2016	Elephant 7	80	*	*	*	*	*	*
11	1/1/2016	Elephant 7s	80	*	*	*	*	*	*
12	1/1/2016	Folly1	120	*	*	*	*	*	*
13	4/1/2016	Folly1	105	*	*	*	*	*	*
14	10/1/2016	Folly1	120	*	*	*	*	*	*
15	4/1/2017	Folly1	118	*	*	*	*	*	*
16	10/1/2017	Folly1	120	*	*	*	*	*	*
17	4/1/2018	Folly1	105	*	*	*	*	*	*
18	10/1/2018	Folly1	120	*	*	*	*	*	*
19	4/1/2019	Folly1	105	*	*	*	*	*	*
20	10/1/2019	Folly1	120	*	*	*	*	*	*
21	4/1/2020	Folly1	105	*	*	*	*	*	*
22	10/1/2020	Folly1	120	*	*	*	*	*	*
23	4/1/2021	Folly1	105	*	*	*	*	*	*
24	10/1/2021	Folly1	120	*	*	*	*	*	*
25	1/1/2016	Folly2	200	*	*	*	*	*	*
Sheet

 

[coloradobstime]

Wow, that is really awesome! I'm going to try and walk myself through that monster of a formula to see if I can understand what it is doing. The absolute values look intriguing. Thank you so much for the help - I don't think even if I threw another 20 hours at it I would have come to a solution.

 

[AhoyNC]

You're welcome. Thanks for the feedback.

This part of the formula:

IF($B$3:$B$25=$G3,IF($A$3:$A$25<=$F3,ABS($A$3:$A$25-$F3)))

Looks for a match in column B unit for the item in G3. It then looks for the dates in column A that are less than or equal to the date in F3, it then subtracts the date in F3 from each of those values and returns the following array:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;0;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
In this case we had an exact match in row 10 which shows up as a 0.

When we put a MIN function in front of the formula it returns the 0.

So the MATCH function looks for the 0 and finds it in row 10 and returns a 10 to the INDEX.
So this part of the formula just returns the row that matches the criteria.

[MATCH(MIN(IF($B$3:$B$25=$G3,IF($A$3:$A$25<=$F3,ABS($A$3:$A$25-$F3)))),IF($B$3:$B$25=$G3,IF($A$3:$A$25<=$F3,ABS($A$3:$A$25-$F3))),0)/CODE]

Hope that helps some.