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
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 14, 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
    Jan 20, 2020

    Twifoo 

     

    Thank you very much!

  • Dexlee46's avatar
    Dexlee46
    Brass Contributor
    Jan 16, 2020

    Twifoo 

     

    Hey Robert,

     

    Sorry I have an additional question for the problem involving finding the latest date from the receiving log. So here is the formula used currently:

     

    =IFNA(LOOKUP(2,1/("Here"'Daily Receiving Log .xlsx'!Daily_Receiving_Log[P.O. No.]=[@1st]),'Daily Receiving Log .xlsx'!Daily_Receiving_Log[Date]),"")

     

    So I'm matching only the PO number, but I will also need to match the part number.

     

    My question is, for this formula I tried adding an add (where I have in bold "here") constraint right after the look up function, matching the function match the PO and the part number, but it didn't not work. Is there another way to create two constraints for this formula so I'm not getting incorrect date information?

     

    Thanks.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jan 03, 2020
    You’re very much welcome!
  • Dexlee46's avatar
    Dexlee46
    Brass Contributor
    Jan 02, 2020

    Twifoo

     

    Just implemented it successfully.

     

    Thank you very much for your help! 

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jan 02, 2020
    The table name is Receiving while those enclosed in brackets are Column Names. Such is the syntax for structured references to Excel Tables.
  • Dexlee46's avatar
    Dexlee46
    Brass Contributor
    Jan 02, 2020

    Twifoo 

     

    Sorry I'm still confused on the syntax for referencing my receiving log. How is the syntax organized for referencing the log? 

     

    The part of the formula I'm confused about is: Receiving[P.O. No.] & Receiving[Date].

     

    Would you also want to jump on a skype call, in case I'm not explaining this very well.

     

    Thanks.

  • 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!

Resources