Vlookup value after comma+space and return each value seperated by comma+space till the last comma+space in the cell

[sandeepsandeep]

I have a query which I am unable to solve.

I have certain Values in Sheet1 in Col A as below
991121QR5, 991121XSX, 991234SSC, 991121SDF

In Sheet2 I have following Values in Col A and B
991121QR5 100100
991121SDF 121212
991234SSC 123456
991121XSX 888888

I want to vlookup each value after comma+space with the table in Sheet2 till the last comma+space in the series.

So when I enter the following in Col A
991121QR5, 991121XSX, 991234SSC, 991121SDF

Result should look like this in Col B
100100, 888888, 123456, 121212

Thanks in advance.

 

[sandeepsandeep]

But what if my excel is in this way.

 

[oldbrewer]

I have not posted the formula, yet

 

[oldbrewer]

if you cannot change how the data is, chop it up in helper columns using

=left(a1,search(",",a1)-1))

and for the rest use the mid function with search

 

[sandeepsandeep]

if you cannot change how the data is, chop it up in helper columns using

=left(a1,search(",",a1)-1))

and for the rest use the mid function with search

This does not work

 

[oldbrewer]

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl22, width: 64"]444,555[/TD]
[TD="width: 64"]444[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula finding 444[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=LEFT(A1,SEARCH(",",A1)-1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sorry I had extra ")" on the end of the formula[/TD]
[/TR]
</tbody>[/TABLE]

 

[lhartono]

I think it is easier to create user defined function (vba).
if is it ok with you.

 

[sandeepsandeep]

I think it is easier to create user defined function (vba).
if is it ok with you.

Thats fine with me. Please provide the code

 

[Jon21990]

Yeah it's a pretty terrible design.

I'd use something like

=VLOOKUP(LEFT(A2,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(MID(Sheet1!A2,12,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(MID(A2,23,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(RIGHT(Sheet1!A2,9),Sheet2!A:B,2,FALSE)

That will complete the "output" column.

 

[sandeepsandeep]

Yeah it's a pretty terrible design.

I'd use something like

=VLOOKUP(LEFT(A2,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(MID(Sheet1!A2,12,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(MID(A2,23,9),Sheet2!A:B,2,FALSE)&", "&VLOOKUP(RIGHT(Sheet1!A2,9),Sheet2!A:B,2,FALSE)

That will complete the "output" column.

Nope that does not work

 

[oldbrewer]

my solution does work......