convert my current formula into a dynamic array formula

[ED38]

Hello,

I would need your help to convert my current formula into a dynamic array formula (formula in column AE)
My formula works but it would be much more convenient if it were an array formula as i plan to use it in a large data base...
thanks for your help.

I am combining the product code and the tier because when using the product code only, because when the several occurences of the same date, the next ranks (n+1, +2, +3...) are skipped and then not found by the formula but if you have other ideas, I am open!

2024-11-18 COGS Recency Report connect to DB.xlsx
	AA	AB	AC	AD	AE	AF	AG
1	Product	Tier	Value	Updated Date	Valid up to	Next updated date	Product-Tier rank
2	A	2	110	24/01/23	20/09/24	20/09/24	4
3	B	1	769	08/11/24	30/01/25	30/01/25	1
4	C	2	177	13/08/23	26/09/23	26/09/23	2
5	B	2	905	08/11/24	30/01/25	30/01/25	1
6	A	2	685	09/01/25
7	C	1	111	22/10/22
8	B	2	120	23/03/24
9	A	1	396	30/04/23
10	B	1	878	03/09/23
11	C	2	64	27/09/23
12	A	2	384	21/09/24
13	A	2	10	06/10/24
14	C	1	108	04/12/24
15	B	1	23	01/08/23
16	C	1	186	19/12/22
17	B	1	769	01/09/24
Sheet1

Cell Formulas
Range	Formula
AG1	AG1	=AA1&"-"&AB1&" rank"
AE2:AE5	AE2	=IFERROR(LARGE(IF($AA$2:$AA$17&$AB$2:$AB$17=$AA2&$AB2,$AD$2:$AD$17),SUMPRODUCT(($AA$2:$AA$17&$AB2:$AB17=$AA2&$AB2)*($AD2<$AD$2:$AD$17))),TODAY())-1
AF2:AF5	AF2	=IFERROR(LARGE(IF($AA$2:$AA$17&$AB$2:$AB$17=$AA2&$AB2,$AD$2:$AD$17),AG2-1),TODAY())-1
AG2:AG5	AG2	=SUMPRODUCT(($AA$2:$AA$17&$AB2:$AB17=$AA2&$AB2)*($AD2<$AD$2:$AD$17))+1

 

[HongRu]

Try it.

=LET(
    _Prodcut_Tier, $AA$2:$AA$17 & $AB$2:$AB$17,
    _Updated_Date, $AD$2:$AD$17,
    _Result, MAP(
        _Prodcut_Tier,
        _Updated_Date,
        LAMBDA(a, b,
            IFERROR(
                LARGE(
                    IF(_Prodcut_Tier = a, _Updated_Date),
                    SUMPRODUCT((_Prodcut_Tier = a) * (b < _Updated_Date))
                ),
                TODAY()
            ) - 1
        )
    ),
    _Result
)

 

[ED38]

That works fine!
Thanks a lot HongRu