Search, index, filter formula

[valmir]

Hello everyone,
I have this formula:
=IF(ISERROR(SEARCH(","; D2)); D2; INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); COUNTA(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2))) & " - " & INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); 1))
And the data in D2 is:
2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007
And the result is:
2007 - 2000
(basically the formula returns the last year followed by dash and the first year)
Now, how can this formula be changed so that I get "2000 - 2007", instead of "2007 - 2000"?
This formula is also returning a single value with no dash if that's the case (as desired)
Thanking you in advance

 

[Fluff]

Maybe

=IF(ISERROR(SEARCH(","; D2)); D2; INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); 1)&" - "&INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); COUNTA(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2))))

But if you just want the 1st & last years from the data in D2 how about

Fluff.xlsm
	D	E
1
2	2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007	2000 - 2007
3	2010	2010
Master

Cell Formulas
Range	Formula
E2	E2	=LET(x,TRIM(TEXTSPLIT(D2,,",")),IF(ROWS(x)=1,x,TEXTJOIN(" - ",,TAKE(x,1),TAKE(x,-1))))
E3	E3	=LET(x,TRIM(TEXTSPLIT(D3,,",")),IF(ROWS(x)=1,x,TEXTJOIN(" - ",TAKE(x,,1),TAKE(x,-1))))

 

[valmir]

Maybe

=IF(ISERROR(SEARCH(","; D2)); D2; INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); 1)&" - "&INDEX(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2); COUNTA(FILTER(Seasons!$Q:$Q; Seasons!B:B=Stats!B2))))

But if you just want the 1st & last years from the data in D2 how about

Fluff.xlsm
	D	E
1
2	2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007	2000 - 2007
3	2010	2010
Master

Cell Formulas
Range	Formula
E2	E2	=LET(x,TRIM(TEXTSPLIT(D2,,",")),IF(ROWS(x)=1,x,TEXTJOIN(" - ",,TAKE(x,1),TAKE(x,-1))))
E3	E3	=LET(x,TRIM(TEXTSPLIT(D3,,",")),IF(ROWS(x)=1,x,TEXTJOIN(" - ",TAKE(x,,1),TAKE(x,-1))))

Thanks a lot! The second option also works perfectly and it's simpler!

 

[Fluff]

You're welcome & thanks for the feedback.