SOLVED

Filter using dynamic array as test citeria.

Regular Contributor

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 (Regular Contributor)
Solution

@Geir Hogstad 

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

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

filter lowest date.JPG 

@Geir Hogstad 

As variant

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

@Geir Hogstad 

VLOOKUP with the date sorted in ascending order:

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