Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Filter using dynamic array as test citeria.

Steel Contributor

# Filter using dynamic array as test citeria.

Hi,

The goal here is to find the lowest date in a list, using a unique list for match:

Tested with =MIN(FILTER(\$D\$3:\$D\$32;\$C\$3:\$C\$32=G3#;"")) which gives an error.

=MIN(FILTER(\$D\$3:\$D\$32;\$C\$3:\$C\$32=G3;"") work, but the I have to copy the formual down several times, and I would like to avoid that.

Attached example file.

Best Regards

- Geir

3 Replies
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

# Re: Filter using dynamic array as test citeria.

``=BYROW(G3#,LAMBDA(row,MIN(FILTER(D3:D32,C3:C32=row))))``

This formula refers to G3# and spills the results in my sheet.

# Re: Filter using dynamic array as test citeria.

As variant

``````=LET(
ph, LAMBDA(C3:C32),
dt, LAMBDA(D3:D32),
u, UNIQUE(ph()),
MINIFS(dt(), ph(), u)
)``````

# Re: Filter using dynamic array as test citeria.

VLOOKUP with the date sorted in ascending order:

``=LET(data,SORT(C3:D32,2,1),VLOOKUP(G3#,data,2,0))``
1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

# Re: Filter using dynamic array as test citeria.

``=BYROW(G3#,LAMBDA(row,MIN(FILTER(D3:D32,C3:C32=row))))``

This formula refers to G3# and spills the results in my sheet.