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

Hi KONG84 

(Your sheet setup isn't ideal. Would be better if formatted as Table)

Thanks

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
)
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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)
        )
    )
)

     

    • KONG84's avatar
      KONG84
      Copper Contributor
      Jun 26, 2022
      Can you explain ItemCol refer to which cell? Thanks
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 27, 2022

        KONG84 

         

        Re. Can you explain ItemCol refer to which cell? 

        With the workbook I shared this refers to B3:B33 or C3:C33 or D3:D33 or... depending on the result of the XMATCH in formula:

        INDEX(Table1,,XMATCH(CritIem,Table1[#Headers]))

         

Resources