How pull data into a list using a date range without using a pivot table

[bomijoff]

Sorry for the terrible thread name!
The plan is on a Thursday find out which unique refs there are for the following week (L3 is this monday L4 is this Sunday)

So i'm looking to populate column G with all the unique refs that have column C's date between the dates of L3&L4.
I would then lookup the date/address/project type based on the unique ref. (i know how to do this part)

Columns B:E are raw data.
Columns G:J is the ideal outcome.

Is it possible to do this?
Thanks for reading.

 

[etaf]

as you have 365 version

a filter() should work

=FILTER(B3:E14,((C3:C14>=L2)*(C3:C14<=L3)))

you can also add a unique() - but tghe data says its unique anyway

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							output
2		ref	date	address	proj							4/3/23
3		A-1	4/1/23	street1	a		A-3	4/3/23	street3	c		4/5/23
4		A-2	4/2/23	street2	b		A-4	4/4/23	street4	a
5		A-3	4/3/23	street3	c		A-5	4/5/23	street5	b
6		A-4	4/4/23	street4	a
7		A-5	4/5/23	street5	b
8		A-6	4/6/23	street6	c
9		A-7	4/7/23	street7	a
10		A-8	4/8/23	street8	b
11		A-9	4/9/23	street9	c
12		A-10	4/10/23	street10	a
13		A-11	4/11/23	street11	b
14		A-12	4/12/23	street12	c
15
Sheet2

Cell Formulas
Range	Formula
G3:J5	G3	=FILTER(B3:E14,((C3:C14>=L2)*(C3:C14<=L3)))
L2	L2	=TODAY()+4
L3	L3	=TODAY()+6
Dynamic array formulas.

​

 

[bomijoff]

That works perfect! Thank you so much.

 

[etaf]

you are welcome