Lookup Date Based on Reference Cell

[Steve 1962]

Hi

Need a formula that displays a list of dates, based on a commencement value in another cell.

The list of data is in cells A2 to B20 (this table has dates added to it - it is expandable). The date that I want to lookup is in cell D2. I need the output to look like the values in cells A22 to A36. Every time the value in cell D2 is changed, the output list would change accordingly, with the commencement or lookup date (cell D2) appearing first in cell A22.

Thanks

Book1
	A	B	C	D
1	Date	CASH
2	14/11/2022	$188,836		18/11/2022
3	15/11/2022	$188,851
4	16/11/2022	$188,895
5	17/11/2022	$188,909
6	18/11/2022	$188,924
7	21/11/2022	$188,938
8	22/11/2022	$188,960
9	23/11/2022	$189,004
10	24/11/2022	$189,019
11	25/11/2022	$189,034
12	28/11/2022	$189,048
13	29/11/2022	$189,063
14	30/11/2022	$189,114
15	1/12/2022	$189,129
16	2/12/2022	$189,143
17	5/12/2022	$189,165
18	6/12/2022	$189,180
19	7/12/2022	$189,224
20	8/12/2022	$189,245
21
22	18/11/2022
23	21/11/2022
24	22/11/2022
25	23/11/2022
26	24/11/2022
27	25/11/2022
28	28/11/2022
29	29/11/2022
30	30/11/2022
31	1/12/2022
32	2/12/2022
33	5/12/2022
34	6/12/2022
35	7/12/2022
36	8/12/2022
Sheet5

 

[Peter_SSs]

Like this?

22 12 15.xlsm
	A	B	C	D
1	Date	CASH
2	14/11/2022	$188,836		18/11/2022
3	15/11/2022	$188,851
4	16/11/2022	$188,895
5	17/11/2022	$188,909
6	18/11/2022	$188,924
7	21/11/2022	$188,938
8	22/11/2022	$188,960
9	23/11/2022	$189,004
10	24/11/2022	$189,019
11	25/11/2022	$189,034
12	28/11/2022	$189,048
13	29/11/2022	$189,063
14	30/11/2022	$189,114
15	1/12/2022	$189,129
16	2/12/2022	$189,143
17	5/12/2022	$189,165
18	6/12/2022	$189,180
19	7/12/2022	$189,224
20	8/12/2022	$189,245
21
22	18/11/2022
23	21/11/2022
24	22/11/2022
25	23/11/2022
26	24/11/2022
27	25/11/2022
28	28/11/2022
29	29/11/2022
30	30/11/2022
31	1/12/2022
32	2/12/2022
33	5/12/2022
34	6/12/2022
35	7/12/2022
36	8/12/2022
37
Dates

Cell Formulas
Range	Formula
A22:A36	A22	=FILTER(A2:A20,A2:A20>=D2)
Dynamic array formulas.

 

[Peter_SSs]

The list of data is in cells A2 to B20 (this table has dates added to it - it is expandable).

I didn't really allow for that. It would, of course, mean that the output list would not always be able to start in A22. Perhaps something more like this. Make the original data into a formal table and relocate the output.

22 12 15.xlsm
	A	B	C	D	E	F
1	Date	CASH
2	14/11/2022	$188,836		18/11/2022		18/11/2022
3	15/11/2022	$188,851				21/11/2022
4	16/11/2022	$188,895				22/11/2022
5	17/11/2022	$188,909				23/11/2022
6	18/11/2022	$188,924				24/11/2022
7	21/11/2022	$188,938				25/11/2022
8	22/11/2022	$188,960				28/11/2022
9	23/11/2022	$189,004				29/11/2022
10	24/11/2022	$189,019				30/11/2022
11	25/11/2022	$189,034				1/12/2022
12	28/11/2022	$189,048				2/12/2022
13	29/11/2022	$189,063				5/12/2022
14	30/11/2022	$189,114				6/12/2022
15	1/12/2022	$189,129				7/12/2022
16	2/12/2022	$189,143				8/12/2022
17	5/12/2022	$189,165
18	6/12/2022	$189,180
19	7/12/2022	$189,224
20	8/12/2022	$189,245
21
22
Dates

Cell Formulas
Range	Formula
F2:F16	F2	=FILTER(Table1[Date],Table1[Date]>=D2)
Dynamic array formulas.

 

[Steve 1962]

Thanks Peter.

I must be doing something wrong on my end. The output cells are giving #SPILL!.

I'll keep trying to find what I've done wrong.

 

[Steve 1962]

Hi Peter

Not sure what I was doing wrong but it is working fine now.

Appreciate your help. Thanks.

 

[Peter_SSs]

Not sure what I was doing wrong ..

You were probably copying the formula down the column. It needs to go in the top cell only and the other results will (correctly) 'spill' down the column by themselves.
Anyway, glad you got it sorted.

Appreciate your help. Thanks.

You're welcome. Thanks for the follow-up.