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]

444,555	444
	formula finding 444
	=LEFT(A1,SEARCH(",",A1)-1)
				sorry I had extra ")" on the end of the formula

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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......