Finding the most recent date that a number matches a certain criteria.

[terryfold]

Hi there, first time posting so apologies if my etiquette is incorrect.

I'm trying to use index match to find the most recent date that a number is below a specified level. For example, I have the following array:

Col A Col B Col C Col D Col E Col F

	ADN	AAV	ARE	ACR-U	AGF/B
03/04/2018	19.2	3.95	18.3	11.43	6.48
04/04/2018	19.02	3.94	18.75	11.33	6.39
05/04/2018	19.05	4.09	18.45	11.33	6.52
06/04/2018	19.07	4.01	18.36	11.25	6.48
09/04/2018	19.31	3.98	18.29	11.26	6.43

<tbody>
</tbody>

and let's say I would like to identify the most recent date that a number listed below ARE was less than 18.35 - so I would need the formula to return "09/04/2018". How can I do this dynamically?

First, I won't always know which Column I will be in - I will have hundreds of these and so I used match to find the correct column. For example, match("ARE", $1:$1) returned 4, and I know how to do this for every stock I need. I don't know how to incorporate this column result into Index Match.

I then tried index(A2:A6, match(18.35, D2:D6)) and this returns 03/04/2018, but that's not what I'm looking for. If I use a 0 or -1 type of match I get #N/A. This also doesn't incorporate my matching from before for column 4 which I need to have in there for ease of use.

Any advice here? I hope my question is clear. Thank you!

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I
1		AND	AAV	ARE	ACR-U	AGF/B		Stock:	ARE
2	03/04/3018	19.2	3.95	18.3	11.43	6.48		Threshold:	18.35
3	04/04/2018	19.02	3.94	18.75	11.33	6.39		Date:	09/04/2018
4	05/04/2018	19.05	4.09	18.45	11.33	6.52
5	06/04/2018	19.07	4.01	18.36	11.25	6.48
6	09/04/2018	19.31	3.98	18.29	11.26	6.43
Sheet2

Cell Formulas
Range	Formula
I3	{=IFERROR(INDEX($A$2:$A$6,MAX(IF(INDEX($A$2:$F$6,,MATCH($I$1,$1:$1,0))<$I$2,ROW($A$2:$A$6)-ROW($A2)+1))),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[terryfold]

	A	B	C	D	E	F	G	H	I
1		AND	AAV	ARE	ACR-U	AGF/B		Stock:	ARE
2	03/04/3018	19.2	3.95	18.3	11.43	6.48		Threshold:	18.35
3	04/04/2018	19.02	3.94	18.75	11.33	6.39		Date:	09/04/2018
4	05/04/2018	19.05	4.09	18.45	11.33	6.52
5	06/04/2018	19.07	4.01	18.36	11.25	6.48
6	09/04/2018	19.31	3.98	18.29	11.26	6.43

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula I3 {=IFERROR(INDEX($A$2:$A$6,MAX(IF(INDEX($A$2:$F$6,,MATCH($I$1,$1:$1,0))<$I$2,ROW($A$2:$A$6)-ROW($A2)+1))),"Not found")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

WBD

Hi wideboydixon,

Thanks for the response. This worked perfectly for this array, but do you know why it might not carry over properly to a larger array? I thought a sample would be good enough for me to extrapolate for my case, but I actually have an array from C8:SI356, where C8:C356 are the dates and Row 5 (beginning in column D) are the Stocks. When I used the above formula, I'd never get an error but the dates returned wouldn't be correct. It would generally give me April 3, 2018, the first date in the entire array (even if that wasn't the correct case).

I have pre-entered future dates, but I don't see that giving an error as e.g. if the price of ARE was $19.00 today, it'll just fill in every future date.

Is there something I'm missing to extend the formula to a larger/different range? Thank you for your help.

 

[wideboydixon]

Extend from 12 to 356 and it should be working:

Book1
	A	B	C	D	E	F	G	H
1	Stock:	ARE
2	Threshold:	18.35
3	Date:	09/04/2018
4
5				AND	AAV	ARE	ACR-U	AGF/B
6
7
8			03/04/3018	19.2	3.95	18.3	11.43	6.48
9			04/04/2018	19.02	3.94	18.75	11.33	6.39
10			05/04/2018	19.05	4.09	18.45	11.33	6.52
11			06/04/2018	19.07	4.01	18.36	11.25	6.48
12			09/04/2018	19.31	3.98	18.29	11.26	6.43
Sheet2

Cell Formulas
Range	Formula
B3	{=IFERROR(INDEX($C$8:$C$12,MAX(IF(INDEX($A$8:SI$12,,MATCH($B$1,$5:$5,0))<$B$2,ROW($C$8:$C$12)-ROW($C$8)+1))),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[terryfold]

Stock	Threshold	Date
AIM	2.00	Not Found
				ADN	AIM
			Dates
			April 3, 18	19.2	1.68
			April 4, 18	19.02	1.61
			April 5, 18	19.05	1.58
			April 6, 18	19.07	1.6081
			April 9, 18	19.31	1.6242
			April 10, 18	19.35	1.6678
			April 11, 18	19.31	1.73
			April 12, 18	19.19	1.69
			April 13, 18	19.12	1.7
			April 16, 18	19.04	1.72
			April 17, 18	19.19	1.75
			April 18, 18	19.05	1.69
			April 19, 18	19.34	1.71
			April 20, 18	19.16	1.76
			April 23, 18	19.25	1.77
			April 24, 18	19.38	1.75
			April 25, 18	19.4	1.96
			April 26, 18	19.41	1.9
			April 27, 18	19.52	2.072
			April 30, 18	19.69	2.21
			May 1, 18	19.81	2.09

<tbody>
</tbody>

My formula in C2 is:
{=IFERROR(INDEX($D$8:$D$28,MAX(IF(INDEX($D$8:$E$28,,MATCH($A2, $5:$5,0))<$B2,ROW($D$8:$D$28)-ROW($D8)+1))), "Not Found")}

Before I was getting January 0, 00. I'm now just getting an error. I'm hoping to get April 26, 18 as a result. Any thoughts? Really appreciate your help.

 

[terryfold]

Above should say D8:F28, not D28:E28, where appropriate in the formula. Can't edit my posts, sorry.

 

[wideboydixon]

Book1
	A	B	C	D	E	F
1	Stock	Threshold	Date
2	AIM	2	April 26, 18
3
4
5					ADN	AIM
6
7				Dates
8				April 3, 18	19.2	1.68
9				April 4, 18	19.02	1.61
10				April 5, 18	19.05	1.58
11				April 6, 18	19.07	1.6081
12				April 9, 18	19.31	1.6242
13				April 10, 18	19.35	1.6678
14				April 11, 18	19.31	1.73
15				April 12, 18	19.19	1.69
16				April 13, 18	19.12	1.7
17				April 16, 18	19.04	1.72
18				April 17, 18	19.19	1.75
19				April 18, 18	19.05	1.69
20				April 19, 18	19.34	1.71
21				April 20, 18	19.16	1.76
22				April 23, 18	19.25	1.77
23				April 24, 18	19.38	1.75
24				April 25, 18	19.4	1.96
25				April 26, 18	19.41	1.9
26				April 27, 18	19.52	2.072
27				April 30, 18	19.69	2.21
28				May 1, 18	19.81	2.09
Sheet1

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX($D$8:$D$28,MAX(IF(INDEX($A$8:SI$28,,MATCH($A$2,$5:$5,0))<$B$2,ROW($D$8:$D$28)-ROW($D$8)+1))),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Peter_SSs]

Assuming the dates are real dates & not text, here is another option.

Excel Workbook
	A	B	C	D	E	F
1	Stock	Threshold	Date
2	AIM	2	26/04/2018
3
4
5					ADN	AIM
6
7				Dates
8				3/04/2018	19.2	1.68
9				4/04/2018	19.02	1.61
10				5/04/2018	19.05	1.58
11				6/04/2018	19.07	1.6081
12				9/04/2018	19.31	1.6242
13				10/04/2018	19.35	1.6678
14				11/04/2018	19.31	1.73
15				12/04/2018	19.19	1.69
16				13/04/2018	19.12	1.7
17				16/04/2018	19.04	1.72
18				17/04/2018	19.19	1.75
19				18/04/2018	19.05	1.69
20				19/04/2018	19.34	1.71
21				20/04/2018	19.16	1.76
22				23/04/2018	19.25	1.77
23				24/04/2018	19.38	1.75
24				25/04/2018	19.4	1.96
25				26/04/2018	19.41	1.9
26				27/04/2018	19.52	2.072
27				30/04/2018	19.69	2.21
28				1/05/2018	19.81	2.09
Most Recent