Formula Help to count dates between matching outcomes

[jec86]

Hello all! I'm trying find the correct formula to deliver a counted value for the amount of time that has passed since the last time a desired value was shown. For instance, today is 2/11/2021 and I'm trying to figure out how many days have past since the last time "3" was realized. 20 would be the correct amount of days that the formula should solve for (2/11/2021 through 1/23/2021). Anyone have a simple formula to accomplish this?

 

[KRice]

Welcome to the board! Here is one approach...

MrExcel20210209.xlsx
	A	B	C	D	E	F
1	Date	Number	Search for -->	3	20	<-- Days since
2	2/11/2021	7
3	2/10/2021	0
4	2/9/2021	4
5	2/8/2021	5
6	2/7/2021	8
7	2/6/2021	4
8	2/5/2021	6
9	2/4/2021	4
10	2/3/2021	7
11	2/2/2021	5
12	2/1/2021	8
13	1/31/2021	2
14	1/30/2021	0
15	1/29/2021	5
16	1/28/2021	8
17	1/27/2021	7
18	1/26/2021	4
19	1/25/2021	2
20	1/24/2021	4
21	1/23/2021	9
22	1/22/2021	3
23	1/21/2021	5
24	1/20/2021	3
jec86

Cell Formulas
Range	Formula
E1	E1	=DAYS(A2,INDEX($A$2:$A$24,MATCH(D1,$B$2:$B$24,0)))

 

[Phuoc]

Or try

=MATCH(D1,B2:B24,0)-1

 

[KRice]

That works too, provided no calendar days are missing from the list. If any days are skipped, an incorrect answer may be returned.

 

[jec86]

Welcome to the board! Here is one approach...

MrExcel20210209.xlsx
	A	B	C	D	E	F
1	Date	Number	Search for -->	3	20	<-- Days since
2	2/11/2021	7
3	2/10/2021	0
4	2/9/2021	4
5	2/8/2021	5
6	2/7/2021	8
7	2/6/2021	4
8	2/5/2021	6
9	2/4/2021	4
10	2/3/2021	7
11	2/2/2021	5
12	2/1/2021	8
13	1/31/2021	2
14	1/30/2021	0
15	1/29/2021	5
16	1/28/2021	8
17	1/27/2021	7
18	1/26/2021	4
19	1/25/2021	2
20	1/24/2021	4
21	1/23/2021	9
22	1/22/2021	3
23	1/21/2021	5
24	1/20/2021	3
jec86

Cell Formulas
Range	Formula
E1	E1	=DAYS(A2,INDEX($A$2:$A$24,MATCH(D1,$B$2:$B$24,0)))

Thanks KRice! This is exactly what I needed!

 

[KRice]

Happy to help.