Nesting in an Index Match

[john62290]

Hello. I am looking for a formula that searches for the latest date in the column date of a table and returns the order associated with the latest date. For example, in the below table, the last order for Account 2 is 25. Would this be nesting a maxifs in an index match formula? I appreciate your help. (Admin won't allow me to download XL2BB; I am assuming this is Table 1, and the headers are the titles in the table.)

Account	Date	Order
Account 1	1/3/2023​	25
Account 2	2/3/2023	150
Account 1	3/4/2023	25
Account 3	3/19/2023	50
Account 2	4/3/2023	25
Account 2	5/1/2023	25
Account 3	5/12/2023	50

 

[Joe4]

Are you looking to return the latest order for JUST Account 2, or for all three accounts?

 

[john62290]

Are you looking to return the latest order for JUST Account 2, or for all three accounts?

All three accounts. Sorry, should have clarified that. Thank you.

 

[Joe4]

OK, here I show your original data and my results:

Formula in cell F2:

=UNIQUE(A2:A8)

Formula in cell G2 (and copy down to G3:G4):

=FILTER(B2:C8,B2:B8=MAXIFS(B2:B8,A2:A8,F2))

 

[john62290]

OK, here I show your original data and my results:

View attachment 98565

Formula in cell F2:

=UNIQUE(A2:A8)

Formula in cell G2 (and copy down to G3:G4):

=FILTER(B2:C8,B2:B8=MAXIFS(B2:B8,A2:A8,F2))

This should work, but I have more data in my table than here. If the date is in B and the matching orders aren't until column F or G, would the filter be B2:G8?

 

[Joe4]

One of the best ways to learn is to try these things out for yourself.
See here for more help on the FILTER function: FILTER function - Microsoft Support

If you cannot get it to work the way you want, please post an example of your real data structure, and show us your desired result.

 

[john62290]

One of the best ways to learn is to try these things out for yourself.
See here for more help on the FILTER function: FILTER function - Microsoft Support

If you cannot get it to work the way you want, please post an example of your real data structure, and show us your desired result.

I feel like I am close. That formula you shared is for the date. What did you use in column H? Thanks

 

[Joe4]

I feel like I am close. That formula you shared is for the date. What did you use in column H? Thanks

Nothing. The formula in column G is a Spill function that returns multiple columns. So the value in column H comes from the formula in column G.
This is because I am telling the FILTER function to return 2 columns:

=FILTER(B2:C8