Lookup a date range and return array

[MutanGFX]

Hello!

What formula can I write in H2 to have it show me an array containing the date, info and color based on today's date + next 7 days?

Book2
	A	B	C	D	E	F	G	H	I	J
1	Date	Info	Color		Today	20/10/2022		Next 7 days info
2	20/10/2022	JOHN	Blue					Date	Info	Color
3	20/10/2022	KATE	Green					20/10/2022	JOHN	Blue
4	21/10/2022	KEVIN	Purple					20/10/2022	KATE	Green
5	21/10/2022	LOUIS	Yellow					21/10/2022	KEVIN	Purple
6	22/10/2022	PETER	Green					21/10/2022	LOUIS	Yellow
7	23/10/2022	EVAN	Black					22/10/2022	PETER	Green
8	28/10/2022	MARIE	White					23/10/2022	EVAN	Black
9	30/10/2022	JACK	Pink
Sheet3

Cell Formulas
Range	Formula
F1	F1	=TODAY()

 

[Joe4]

Try making use of Excel's new FILTER function.
Place this formula in cell H3:

=FILTER(A2:C9,(A2:A9>=F1)*(A2:A9<=(F1+7)))

See here for an explanation:

FILTER function with two criteria

In this video, we’ll set up the FILTER function with two criteria.

exceljet.net

 

[MutanGFX]

Try making use of Excel's new FILTER function.
Place this formula in cell H3:

=FILTER(A2:C9,(A2:A9>=F1)*(A2:A9<=(F1+7)))

See here for an explanation:

FILTER function with two criteria

In this video, we’ll set up the FILTER function with two criteria.

exceljet.net

Thank you for the solution. For some reason I was tunnel visioning about using INDEX/MATCH or XLOOKUP in someway.

I just recently started using arrays in my projects.

Now FILTER seems like such an obvious solution. Thank you again!

 

[Joe4]

You are welcome.
Yes, FILTER is a great new function that works perfectly in this instance.
The Spill feature ensures that it will automatically populate all the data needed without you have to copy the formula down.