Embedding TEXTSPLIT and TEXTJOIN into an INDEX/MATCH formula

[jimbomcmucka]

Hi guys, I'm hoping you can help - my brain alone wont get me through this problem.

Not so long ago I posted about multiple values being separated in my data source with a '|' and somebody brought my attention to the TEXTSPLIT and TEXTJOIN function, which has helped hugely incorporating into some VLOOKUPS. I am now trying to add these into an INDEX/MATCH formula and am confusing myself.

The current formula is as follows:

=CONCATENATE("model-",(INDEX('Extract (Marketing)'!2:2,1,MATCH("PRODCODE",'Extract (Marketing)'!$A$1:$LX$1,0)))))

So, the value from the column named 'PRODCODE' is returned with the prefixed text "model-"

As with my previous problem there are multiple PRODCODES within the data set separated by a '|'. How would i go about adding the TEXTSPLIT and TEXTJOIN function to return a result as follows:

model-123456789|model-234567890|model-3456789012 ...

Any help is really appreciated!

Thanks,
Jim

 

[Sufiyan97]

What result you are currently getting?

 

[jimbomcmucka]

What result you are currently getting?

Currently, using the formula above, I am getting as follows: "model-123455789|234567890|3456789012"

I am yet to have a result return without an error when trying to add in the TEXTSPLIT and TEXTJOIN.

Thanks,
Jim

 

[Sufiyan97]

Try

=SUBSTITUTE(CONCATENATE("model-",(INDEX('Extract (Marketing)'!2:2,1,MATCH("PRODCODE",'Extract (Marketing)'!$A$1:$LX$1,0)))),"|","|model-")

 

[jimbomcmucka]

Amazing! Thanks for your help. Looks like I was getting bogged down using the wrong function for the task!

Jim

 

[Sufiyan97]

You're welcome.