Forum Discussion
Index and Match with Max Function
Instead of images, please attach your sample Excel file.
- Dexlee46Dec 11, 2019Brass Contributor
I attached both spreadsheets that I'm referencing. I deleted the columns that are applicable for simplicity. Book 2 is the sheet that will be returning values (purchase order log) and Book 3 is the sheet I'm conducting the index and match function (daily receiving log). Again I need to return the latest date, If you look at the bottom of book 2 (PO 1912017) I highlighted blue is the value I'm testing. In book 3 I added three orders for that PO with dates 12/11/2019, 12/12/2019, and 12/13/2019. The returning value in the "date received column" in book 2 should be 12/13/2019. But again I'm not sure how to nest the max function to do this within the index and match. I understand that the max must be nested after the match, but I don't believe excel will allow this, as the max needs an array to reference. But I need the array to be the dates that match with the corresponding look up value that is in the match.
Let me know if this makes sense.
Thanks for your help!
- TwifooDec 13, 2019Silver Contributor
I was busy these past few days so I'm sorry for this delayed reply. In the attached file, I assumed that the dates in your Receiving Log are recorded in chronological order, such that the formula in C2630 is:
=IFNA(LOOKUP(2,1/(
ReceivedOrders[P.O. No.]=[@1st]),
ReceivedOrders[Date]),"")The result of the foregoing formula is 12/13/2019, as shown in the snapshot below: