Forum Discussion

KONG84's avatar
KONG84
Copper Contributor
Jun 24, 2022
Solved

FIND OUT START AND END DATE

 

 

Hi, I have been stuck at this part for a while, anyone have any idea to find out the start and end date for item B when it is in Japan?

Was thinking minifs+hlookup .....

 

Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 24, 2022

    KONG84 

     

    With Excel 2021 you can get both dates spilled with a single formula
    - Data formated as Table1
    - Named M3 & M4 respectively CritItem & CritCountry (not mandatory)

     

    in M5:

    =LET(
    ItemCol, INDEX(Table1,,XMATCH(CritIem,Table1[#Headers])),
    IF(COUNTA(CritIem, CritCountry) < 2, "",
        CHOOSE(SEQUENCE(2),
            MINIFS(Table1[Date], ItemCol, CritCountry),
            MAXIFS(Table1[Date], ItemCol, CritCountry)
        )
    )
)

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 24, 2022

    KONG84 

     

    Purely for the record the following also work as regular formulas assuming [Dates] are sorted as in your initial post

     

    Start date

    =INDEX(Table1[Date],
    MATCH(CritCountry,
        INDEX(Table1,,MATCH(CritIem,Table1[#Headers],0)),
        0
    )
)

    End date

    =LOOKUP(2,
    1/(INDEX(Table1,,MATCH(CritIem,Table1[#Headers],0))=CritCountry),
    Table1[Date]
)
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 24, 2022

    KONG84 

    For those without access to dynamic arrays this might work.  Ctrl+Shift+Enter to define as an array:

     

    =MIN(IF(Table1[[A]:[I]]=CritCountry,IF(Table1[[#Headers],[A]:[I]]=CritIem,Table1[Date])))

    and

    =MAX(IF(Table1[[A]:[I]]=CritCountry,IF(Table1[[#Headers],[A]:[I]]=CritIem,Table1[Date])))

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 24, 2022

    KONG84 

    A variant:

    =LET(f,FILTER(Table1,Table1[#Headers]=CritIem),TRANSPOSE(XLOOKUP(CritCountry,f,Table1[Date],"",,{1,-1})))
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        Jun 27, 2022
        f refers to FILTER(Table1,Table1[#Headers]=CritIem)
        The "" in XLOOKUP will return "" in the event of an error.
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 24, 2022

      KONG84 

       

      Or, with Patrick2788's idea:

      =LET(
    f, FILTER(Table1,Table1[#Headers]=CritIem),
        XLOOKUP(CritCountry,f,Table1[Date],"",,{1;-1})
)

       

    • KONG84's avatar
      KONG84
      Copper Contributor
      Jun 24, 2022

      Lorenzo 
      Hi, I’m using latest 2021 version excel.

      This is a draft data as my company data is confidential but my actual data format is the same as what I posted on the forum, just that the name is changed. I will take your useful advice to set up in table form, but there are 100 items to track so I still need the function to help me work on it . To clarify, start date is earliest date and end date is latest date. 

       

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 24, 2022

        KONG84 

         

        Good point from Patrick2788. If Dynamic Arrays not available, the following work as regular formulas (Enter only) with Excel >/= 2010

         

        Start date:

        =AGGREGATE(15,6,
    Table1[Date] / ((Table1[[#Headers],[A]:[I]]=CritIem) * (Table1[[A]:[I]]=CritCountry)),
    1
)

        End date:

        =AGGREGATE(16,6,
    Table1[Date] / ((Table1[[#Headers],[A]:[I]]=CritIem) * (Table1[[A]:[I]]=CritCountry)),
    1
)

Resources