How to use XLOOKUP with a max Date criteria

Hello Gurus,

I'm trying to use XLOOKUP to index data. Easy enough by itself. However, my data source has multiple rows of the same item number, each with Value Date and Values. How can i index the Value and the Value Date based on the latest Value Date. Please see attached sample file and result i want to achieve.

Try this:

=LET(t,A1:C1,a,A2:C16,m,MAXIFS(C2:C16,A2:A16,A2:A16),VSTACK(t,FILTER(a,INDEX(a,,3)=m)))

Hey buddy -- I've attached what you wanted. It's also dynamic so one column can be copied and its formula can be pasted across the other two columns. Attached.punksters626.xlsx

Formula below:

=INDEX($A$2:$C$16,ROWS(MAX($C$2:$C$16)),COLUMNS($A$8:A8))

That's what my formula does, it just generates the whole table for you.

If you want just those columns, try this:

=LET(a,B3:C17,m,MAXIFS(C3:C17,A3:A17,A3:A17),FILTER(a,INDEX(a,,2)=m))

But then again, maybe this is what you want (assuming you want the minimum value from the maximum dates):

=LET(h,A2:C2,t,A3:C17,a,A3:A17,c,C3:C17,m,MAXIFS(c,a,a),f,FILTER(t,c=m),VSTACK(h,FILTER(f,INDEX(f,,2)=MIN(INDEX(f,,2)))))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Could you provide a more realistic sample table for the results? I cannot see why that would not work for you, so you need to show me.