Using Index Match or Vlookup on a column that has cells with multiple values separated by a Space

[Cknight]

Hi all-

I have been trying to find a solution for this but have come up empty. We have a new system that puts all of our barcodes into one cell in column C (example below) These values are dynamic. Some have 1 barcode and some have 15+. I am trying to find a way to use index match or something similar on another worksheet to search through column C and return column A.

So on worksheet 2, if column A has 048500000007 entered in, column B would return TROPICANA ORANGE JUICE 32OZ

TROPICANA ORANGE JUICE 10OZ	TRORJU1NAECCE1O	048500001745 TRORJU1NAECCE1O
TROPICANA ORANGE JUICE 12OZ	4850001775	4850001775
TROPICANA ORANGE JUICE 32OZ	4850000000	048500000007 025000055423 4850000000

If this is not possible, I would be interested if there is an easy way to use text to columns or something similar to separate column C and create a list with column A repeated for every value in column C that would look like

Column A Column B
TROPICANA ORANGE JUICE 10OZ 048500001745
TROPICANA ORANGE JUICE 10OZ TRORJU1NAECCE1O
TROPICANA ORANGE JUICE 12OZ 4850001775
TROPICANA ORANGE JUICE 32OZ 048500000007
TROPICANA ORANGE JUICE 32OZ 025000055423
TROPICANA ORANGE JUICE 32OZ 025000055423

Thanks so much in advance for any help on this!

 

[bosco_yip]

Try,

1] Set up "Source Table sheet" and "Result Table sheet" as below.

2] In "Result Table sheet" E3, enter formula and copied down :

=LOOKUP(1,-SEARCH(F3,C$3:C$5),A$3:A$5)

Remark: Change the set up as follow your own worksheet, and change the cells address as well.

 

[Cknight]

Try,

1] Set up "Source Table sheet" and "Result Table sheet" as below.

2] In "Result Table sheet" E3, enter formula and copied down :

=LOOKUP(1,-SEARCH(F3,C$3:C$5),A$3:A$5)

Remark: Change the set up as follow your own worksheet, and change the cells address as well.

View attachment 84674

Thanks for the response! Is there a formula for column F as well that column E can reference?

 

[bosco_yip]

Thanks for the response! Is there a formula for column F as well that column E can reference?

As per the forum rule: "one post one question",

Please open a new thread for your new question.

Thanks