Aug 19 2021 03:12 AM
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 :thinking_face:
Many many thanks how to do it
Petr
Aug 19 2021 03:54 AM
Solution@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.
Aug 19 2021 06:10 AM
That shall work on any Excel version
=INDEX(D4:D9, MATCH(AGGREGATE(14,6,1/(C4:C9=G4)*B4:B9,1),B4:B9,0))
Aug 25 2021 04:25 AM
Many thanks, very interesting function for future
Aug 19 2021 03:54 AM
Solution@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.