SOLVED

Look up in Excel

Copper 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 :thinking_face:
Many many thanks how to do it

Petr

3 Replies
best response confirmed by allyreckerman (Microsoft)
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.

@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 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
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.

View solution in original post