split rows and copy value in adjacent column

[kristofke]

Hi

I have a list of EAN codes, but some rows have multiple EAN codes in them. I need to do a vertical search on these to get the price from column B, but this doesn't work because the multiple values in one cell like A3 are text and the vertical search does not return the value from column B if one of the searched EAN codes is for example in A3.

How can I split the rows and copy the price with every EAN code that is split.

For example:

Should become:

 

[KP117]

If you have concat(), consider these formulas with CSE.

At C1 and down
=TRIM(MID(SUBSTITUTE(CONCAT(SUBSTITUTE($A$1:$A$10,CHAR(10),",")&","),",",REPT(" ",LEN(CONCAT(SUBSTITUTE($A$1:$A$10,CHAR(10),",")&",")))),1+(ROW($A1)-1)*LEN(CONCAT(SUBSTITUTE($A$1:$A$10,CHAR(10),",")&",")),LEN(CONCAT(SUBSTITUTE($A$1:$A$10,CHAR(10),",")&","))))

At D1 and down,
=LOOKUP(100,SEARCH(C10,$A$1:$A$10),$B$1:$B$10)