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

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_StartEndDates.xlsx21 KB

Forum Discussion

FIND OUT START AND END DATE

Resources