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

Patrick2788

Silver Contributor

Jun 24, 2022

KONG84

A variant:

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

KONG84
Copper Contributor
Jun 26, 2022
Patrick2788
Hi Patrick, I'm not too sure what does 'f' and '''' refer to.
KONG84
Copper Contributor
Jun 26, 2022
May I know what is 'f'?
- 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

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})
)

Forum Discussion

FIND OUT START AND END DATE

Resources