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...
excel
Formulas and Functions
office 365
Twifoo's avatar
Twifoo
Silver Contributor
Dec 13, 2019
I presume my recommended formula returned your expected results. To satisfy your curiosity, MATCH returns the EXACT (0), NEXT LOWER (1), or NEXT HIGHER (-1) position for each of the the match_type arguments. LOOKUP always returns the LAST match, which is EXACTLY what you are LOOKING for, assuming the dates are recorded chronologically as is always normal in the course of events.
Incidentally, LOOKUP is my favorite function. Let me now declare that the LOOKUP is far better than both the debated choice between VLOOKUP and INDEX-MATCH.
Indubitably, the new and more powerful XLOOKUP simply improves the ability of my favorite LOOKUP.
Dexlee46's avatar
Dexlee46
Brass Contributor
Dec 13, 2019

Twifoo 

 

Sorry I need help one more time to get the syntax correct for referencing this function to the other sheet.

 

I attached both work books with the specified date and PO number. 

Thank very much for your help!

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

    Dexlee46 

    The data and structure of both workbooks you attached are now totally different from those you previously attached. Please specify the cells you need help in constructing the formula and explain the logic of each. Thereafter, I'll try to fathom my suggestion to you. 

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

      Twifoo 

       

      Hello,

       

      Any chance you made any progress in regards to completing the function for the complete spreadsheet I sent over?

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

      Twifoo 

       

      Sorry for the confusion. The cells will be the same the date received cells on the purchasing log will be will where I want to pull the dates from the daily receiving log. I will again need to match the part number and the PO number to pull the correct date, and again I will need to pull the latest date based on the PO number (if there are multiple batch orders).

       

      Thank you again for your help!