TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria

[WhimsicalRaptor87]

Hi all,
I'm using this formula to search multiple ranges that contain dates and if they fall between a set range (=List!C1 and =List!D1 in my example sheet), then return the value from a different column, then join the resulting values:

=TEXTJOIN(", ",TRUE,
IFERROR(INDEX(Input!$N$2:$N$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$M$2:$M$17>=List!$C$1)*(Input!$L$2:$L$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$K$2:$K$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$J$2:$J$17>=List!$C$1)*(Input!$I$2:$I$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$H$2:$H$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$G$2:$G$17>=List!$C$1)*(Input!$F$2:$F$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$E$2:$E$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$D$2:$D$17>=List!$C$1)*(Input!$C$2:$C$17<=List!$D$1),0)),""))

However, I'd like it to return distinct values. So for Luna Starfire, E6 should only say 'Squirtle, Snorlax'. I've attempted adding in UNIQUE many times but it either still returns multiple values or returns an error.

Here is the sheet where I'm attempting to run the formula above in cell E4:E12:

ExampleExcel.xlsx
	A	B	C	D	E
1	Time period	FY2023	1/07/2022	30/06/2023
2
3	Name	Start Date	End Date	Original Pokemon	Current Pokemon
4	Jade Skywalker	30/01/2023	26/05/2023	Pikachu, Snorlax	Snorlax, Pikachu, Snorlax
5	Jade Skywalker	30/01/2020	31/12/2023	Pikachu	Pikachu, Pikachu
6	Luna Starfire	30/08/2020	28/08/2023	Squirtle, Snorlax	Squirtle, Squirtle, Snorlax
7	Maverick Sapphire	17/05/2023	15/11/2023	Snorlax, Squirtle	Squirtle, Snorlax, Squirtle
8	Nova Midnight	28/02/2023	27/02/2024	Eevee	Eevee
9	Orion Thunder	4/05/2022	5/08/2023	Squirtle	Squirtle
10	Phoenix Frost	9/07/2022	7/01/2023	Jigglypuff, Eevee, Meowth	Meowth, Jigglypuff, Eevee, Meowth
11	Willow Dreamer	30/03/2023	4/06/2023	Mewtwo, Eevee	Mewtwo, Eevee
12	Zephyr Wanderer	16/11/2022	17/05/2023	Mewtwo, Eevee	Mewtwo, Eevee, Meowth, Mewtwo, Eevee
List

Cell Formulas
Range	Formula
A4:C12	A4	=SORT(UNIQUE(FILTER(CHOOSE({1,2,3},Input!A2:A17&" "&Input!B2:B17,Input!C2:C17,Input!D2:D17),(Input!D2:D17>=List!C1)*(Input!C2:C17<=List!D1))))
D4:D12	D4	=IFERROR(INDEX(Input!$E$2:$E$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*(List!$B4=Input!$C$2:$C$17)*(List!$C4=Input!$D$2:$D$17),0)),"")
E4:E12	E4	=TEXTJOIN(", ",TRUE, IFERROR(INDEX(Input!$N$2:$N$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$M$2:$M$17>=List!$C$1)*(Input!$L$2:$L$17<=List!$D$1),0)),""), IFERROR(INDEX(Input!$K$2:$K$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$J$2:$J$17>=List!$C$1)*(Input!$I$2:$I$17<=List!$D$1),0)),""), IFERROR(INDEX(Input!$H$2:$H$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$G$2:$G$17>=List!$C$1)*(Input!$F$2:$F$17<=List!$D$1),0)),""), IFERROR(INDEX(Input!$E$2:$E$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$D$2:$D$17>=List!$C$1)*(Input!$C$2:$C$17<=List!$D$1),0)),""))
Dynamic array formulas.

And here is my source data:

ExampleExcel.xlsx
	E	F	G	H	I	J	K	L	M	N
1	All Pokemon	Extension 1 start date	Extension 1 end date	Pokemon	Extension 2 start date	Extension 2 end date	Pokemon	Extension 3 start date	Extension 3 end date	Pokemon
2	Pikachu, Snorlax	1/01/2023	31/01/2023	Snorlax
3	Snorlax, Squirtle	1/04/2022	15/11/2023	Squirtle
4	Squirtle, Snorlax	18/12/2020	17/12/2021	Squirtle	18/12/2021	18/05/2022	Squirtle, Snorlax	19/05/2022	28/08/2023	Squirtle
5	Squirtle
6	Jigglypuff, Eevee, Meowth	10/12/2022	7/01/2023	Meowth
7	Eevee
8	Mewtwo, Eevee	1/02/2023	31/03/2023	Meowth	1/04/2023	17/05/2023	Mewtwo, Eevee
9	Mewtwo, Eevee
10	Snorlax
11	Mewtwo
12	Mewtwo, Eevee	23/09/2019	13/10/2019	Mewtwo, Eevee	14/10/2019	12/11/2019	Eevee
13	Jigglypuff, Eevee	1/05/2020	30/06/2020	Jigglypuff
14	Pikachu, Snorlax
15	Pikachu	17/08/2020	16/09/2020	Pikachu
16	Pikachu	29/10/2022	29/10/2023	Pikachu	30/10/2023	29/11/2023	Pikachu	30/11/2023	31/12/2023	Pikachu
17	Squirtle
Input

Thank you!

 

[Anonymous1378]

List!A4 references Input!A2:D17 which is unknown since you excluded it...

Haven't looked in detail, but with the way your source data is set up, I'm going to assume you need to split the text, by the comma delimiter before using unique.

 

[WhimsicalRaptor87]

Thank you! I got it working using this:

=TEXTJOIN(", ",, UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,
IFERROR(INDEX(Input!$N$2:$N$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$M$2:$M$17>=List!$C$1)*(Input!$L$2:$L$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$K$2:$K$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$J$2:$J$17>=List!$C$1)*(Input!$I$2:$I$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$H$2:$H$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$G$2:$G$17>=List!$C$1)*(Input!$F$2:$F$17<=List!$D$1),0)),""),
IFERROR(INDEX(Input!$E$2:$E$17,MATCH(1,($A4=Input!$A$2:$A$17&" "&Input!$B$2:$B$17)*($B4=Input!$C$2:$C$17)*(Input!$D$2:$D$17>=List!$C$1)*(Input!$C$2:$C$17<=List!$D$1),0)),"")),", "),TRUE),,,)