SOLVED

FILTER Function

Visitor

Dear All,

 

I would like to seek advice on the "Filter" formula:
Is that possible to use the filter formula to find the latest pay for each employee (Highlighted in yellow)?2021-10-02_15-29-24.jpg

3 Replies
best response confirmed by John_Lim (Visitor)
Solution

Hi @John_Lim 

I'd go with a helper column on my data to flag the latest as TRUE false, but you could do a mega formula

WynHopkins_0-1633163986528.png

 

@John_Lim 

If the data is sorted by date - as in your example - then I would go for XLOOKUP().

=XLOOKUP(H3,$A$3:$A$9,$B$3:$F$9,,0,-1)

@John_Lim 

As variant, assuming source data is in the table Payments and Person ID are numbers

=LET(
  k, 10000000,
  id, UNIQUE(Payments[Person ID]),
  pos, XMATCH(id*k+DATE(9999,1,1), Payments[Person ID] *k+ Payments[End Date],-1),
 INDEX(Payments, pos, SEQUENCE(,COLUMNS(Payments)))
)