SOLVED

FILTER Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2805172%22%20slang%3D%22en-US%22%3EFILTER%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805172%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20seek%20advice%20on%20the%20%22Filter%22%20formula%3A%3CBR%20%2F%3EIs%20that%20possible%20to%20use%20the%20filter%20formula%20to%20find%20the%20latest%20pay%20for%20each%20employee%20(Highlighted%20in%20yellow)%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-10-02_15-29-24.jpg%22%20style%3D%22width%3A%20943px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314551iFC3AD4A20797CED4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222021-10-02_15-29-24.jpg%22%20alt%3D%222021-10-02_15-29-24.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2805172%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805240%22%20slang%3D%22en-US%22%3ERe%3A%20FILTER%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805240%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172867%22%20target%3D%22_blank%22%3E%40John_Lim%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI'd%20go%20with%20a%20helper%20column%20on%20my%20data%20to%20flag%20the%20latest%20as%20TRUE%20false%2C%20but%20you%20could%20do%20a%20mega%20formula%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22WynHopkins_0-1633163986528.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F314553i553D89DC3CC1C76E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22WynHopkins_0-1633163986528.png%22%20alt%3D%22WynHopkins_0-1633163986528.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805254%22%20slang%3D%22en-US%22%3ERe%3A%20FILTER%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172867%22%20target%3D%22_blank%22%3E%40John_Lim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20data%20is%20sorted%20by%20date%20-%20as%20in%20your%20example%20-%20then%20I%20would%20go%20for%20XLOOKUP().%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(H3%2C%24A%243%3A%24A%249%2C%24B%243%3A%24F%249%2C%2C0%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805322%22%20slang%3D%22en-US%22%3ERe%3A%20FILTER%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172867%22%20target%3D%22_blank%22%3E%40John_Lim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%2C%20assuming%20source%20data%20is%20in%20the%20table%20Payments%20and%20Person%20ID%20are%20numbers%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20k%2C%2010000000%2C%0A%20%20id%2C%20UNIQUE(Payments%5BPerson%20ID%5D)%2C%0A%20%20pos%2C%20XMATCH(id*k%2BDATE(9999%2C1%2C1)%2C%20Payments%5BPerson%20ID%5D%20*k%2B%20Payments%5BEnd%20Date%5D%2C-1)%2C%0A%20INDEX(Payments%2C%20pos%2C%20SEQUENCE(%2CCOLUMNS(Payments)))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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)))
)