Forum Discussion

Dexlee46's avatar
Dexlee46
Brass Contributor
Dec 10, 2019

Index and Match with Max Function

Hello,

 

I have a question in regards to index and match. So I'm referencing information from another spreadsheet which is my company's receiving log using the index and match function. The issue I'm having is I need to also nest the max function within this function. When I do this it prompts me to select my array, but this is determined through a matching purchase order. I'm just trying to pull back the latest date when the purchase order has been reconciled, this is applicable when there are multiple batch shipments of the same order since the order will have multiple shipments of the same part number and company. 

 

An example is we can receive part 123 in quantities of 50 on dates 1/2/2020, 3/3/2020, and 6/5/2020. On the receiving log I will need to pull the 6/5/2020 date as that will be the date when we received all of the parts from that specific order.  I attached a picture of the spreadsheets. The one with multiple colors, is my purchase order log, and contains the index and match function, and the other is the receiving log. I highlighted the columns I'm referencing which would be the date received, and the matching purchase order.

 

If someone could give me some insight on how to proceed, it would be much appreciated.

Thanks!

excel
Formulas and Functions
office 365

19 Replies

    • Dexlee46's avatar
      Dexlee46
      Brass Contributor
      Dec 11, 2019

      Twifoo 

       

      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!

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Dec 13, 2019

        Dexlee46 

        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: 

Resources