Look up in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2664362%22%20slang%3D%22en-US%22%3ELook%20up%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2664362%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20can%20you%20please%20advice%20how%20can%20I%20find%20following%20result%26nbsp%3B%3CBR%20%2F%3ESimilar%20to%20Vlookup%20where%20I%20would%20like%20to%20find%20the%20freshest%20price%20amount%20in%20the%20list%20of%20duplicit%20PN%20numbers%2C%20where%20is%20also%20date.%26nbsp%3B%3C%2FP%3E%3CP%3EVLookUp%20give%20me%20always%20the%20oldest%20one%20(first%20one%20in%20the%20list%20of%20duplicit%20possibilities%20%3Athinking_face%3A%3CBR%20%2F%3EMany%20many%20thanks%20how%20to%20do%20it%3C%2FP%3E%3CP%3EPetr%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2664362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2664563%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20up%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2664563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132182%22%20target%3D%22_blank%22%3E%40dostik%3C%2FA%3E%26nbsp%3BPerhaps%20your%20Excel%20versions%20support%20the%20%22new%22%20XLOOKUP%20function.%20That%20one%20allows%20you%20to%20search%20from%20the%20bottom%20up.%20If%20not%2C%20you%20can%20always%20sort%20the%20list%20in%20descending%20order%20by%20date.%20Then%2C%20the%20first%20match%20that%20VLOOKUP%20finds%20will%20be%20the%20one%20with%20the%20most%20recent%20date.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665035%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20up%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1132182%22%20target%3D%22_blank%22%3E%40dostik%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20shall%20work%20on%20any%20Excel%20version%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20652px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304417i7E7AA315FE45983D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(D4%3AD9%2C%20MATCH(AGGREGATE(14%2C6%2C1%2F(C4%3AC9%3DG4)*B4%3AB9%2C1)%2CB4%3AB9%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, can you please advice how can I find following result 
Similar to Vlookup where I would like to find the freshest price amount in the list of duplicit PN numbers, where is also date. 

VLookUp give me always the oldest one (first one in the list of duplicit possibilities
Many many thanks how to do it

Petr

3 Replies

@dostik Perhaps your Excel versions support the "new" XLOOKUP function. That one allows you to search from the bottom up. If not, you can always sort the list in descending order by date. Then, the first match that VLOOKUP finds will be the one with the most recent date.

@dostik 

That shall work on any Excel version

image.png

=INDEX(D4:D9, MATCH(AGGREGATE(14,6,1/(C4:C9=G4)*B4:B9,1),B4:B9,0))

 

@Riny_van_Eekelen 

Many thanks, very interesting function for future