combining a textjoin(with ifs) and LEFT formula

[paulpfl]

I am attempting to combine the two below steps to reduce two columns to one.
Maybe, I am going about this all wrong but I am looking for a solution to look for a value that is on L List in the column for ref_num but I have to separate it from those numbers. If it's there, display just the match. If it's not there, display this first three numbers from T2.

=TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,""))
=IF(V2="",LEFT(T2,3),V2)

When I tried this formula to combine it (Unsuccessful Attempt):
=TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,LEFT(T2,3)))
I'd get this answer:

022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022,022

Thank you in advanced! I have been stuck on a few issues with this sheet on and off for a couple weeks and this one is at the top of my list.

 

[Max1616]

Try this:

IF(TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,""))="",LEFT(T2,3),TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,"")))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[paulpfl]

Try this:

IF(TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,""))="",LEFT(T2,3),TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,"")))

Thank you so much!!

 

[paulpfl]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I apologize! I will update now. Thank you for the tip. I am using Microsoft 365 MSO

 

[Fluff]

Thanks for that.
You could also simplify that formula like

=LET(t,TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,"")),IF(t="",LEFT(T2,3),t))

 

[paulpfl]

Thanks for that.
You could also simplify that formula like

=LET(t,TEXTJOIN(",",TRUE,IF(COUNTIF(S2,"*"&L_List&"*"), L_List,"")),IF(t="",LEFT(T2,3),t))

Woah... gosh I had no idea LET existed.. Off to youtube, to watch 10 videos on it! lol Thank you so much, I really appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback.