Forum Discussion

purohitchetan's avatar
purohitchetan
Copper Contributor
Feb 04, 2021

Excel Formula

Spreadsheet 1 : Purchase register : Date , Supplier, Item, Quantity & Rate (source) Spreadsheet 2 : Material Requirement Planning : Item & Quantity Required (destination)   How to fetch latest dat...
Formulas and Functions
mathetes's avatar
mathetes
Gold Contributor
Feb 04, 2021

purohitchetan 

 

Do you have the FILTER function available? It's in the latest updates to Excel 365. If so, you could try filtering with two criteria

  • Item #=item you need
  • date = MAX(Date)

 

Without an actual example from you of your actual spreadsheets, it's hard to be more specific.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 04, 2021

      purohitchetan 

      If without dynamic arrays that could be

      =INDEX(Sheet1!$B:$B,
     MATCH(1,
        INDEX( (Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))=
            MAX(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))))*
               (Sheet1!$C$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$A:$A))=$A2), 0), 0)
)

       

      • purohitchetan's avatar
        purohitchetan
        Copper Contributor
        Feb 05, 2021

        SergeiBaklan Thanks Buddy, could you please advice logic behind such formula construct, unable to get the result. (Index, Match, Index, Index, Counta, Max)

    • mathetes's avatar
      mathetes
      Gold Contributor
      Feb 04, 2021

      purohitchetan 

       

      Attached shows how FILTER would work in that small example file. You will no doubt need to extend or modify the absolute and relative references in a more fleshed out spreadsheet.

       

      Here's a YouTube video that Microsoft put out to explain these dynamic range functions. It will help you more fully understand how to use FILTER and some of the other associated functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

       

       

       

      • Justin-GOV's avatar
        Justin-GOV
        Brass Contributor
        Feb 05, 2021

        mathetes This worked really well! Thanks for your work on this! This conversation caught my eye and I can see I will be using this example for things I work on.

         

        This was a helpful article as well. (I didn't understand filtering on multiple items at first.)

        https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?ui=en-us&rs=en-us&ad=us#:~:text=Syntax:%20FILTER(array,%20include,%20[if_empty])%20Example:%20=FILTER(A5:D20,%20(C5:C20=H1)*(A5:A20=H2)%20Description:,data%20based%20on%20criteria%20you%20define.%20See%20More

         

        I converted the range to a table so as to expand the array as it grew and make for easier reading in the formula.

         

         

Resources