Oct 02 2021 12:42 AM
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)?
Oct 02 2021 01:40 AM
SolutionHi @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
Oct 02 2021 01:48 AM
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)
Oct 02 2021 03:16 AM
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)))
)
Oct 02 2021 01:40 AM
SolutionHi @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