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.

 

[Jon21990]

Would you be able to deconstruct the data on a separate tab using text to columns, do individual vlookups, and the concatenate the answers?

 

[oldbrewer]

is A1=991121qr5 and A2 991121xsx etc or are all 4 numbers in A1

 

[Jon21990]

If the sheet 2 info is split over two columns, wouldn't you be able to use a vlookup with a left/ mid/ right? Is the len for all codes in sheet1 the same?

 

[sandeepsandeep]

Sheet1 would look like following

A	B	C
CODES	FINAL OUTPUT	TOTAL
991121QR5, 991121XSX, 991234SSC, 991121SDF
991121QR5, 991121XSX, 991234SSC
991121QR5, 991121XSX, 991234SSC, 991121SDF

<colgroup><col width="297" style="width: 223pt;"><col width="97" style="width: 73pt;"><col width="46" style="width: 35pt;"></colgroup><tbody>
</tbody>

Sheet2 has following Table

A	B
CODES	Value
991121QR5	100100
991121SDF	121212
991234SSC	123456
991121XSX	888888

<colgroup><col width="87" style="width: 65pt;"><col width="55" style="width: 41pt;"></colgroup><tbody>
</tbody>

I want Final Output as foll

A	B	C
CODES	FINAL OUTPUT	TOTAL
991121QR5, 991121XSX, 991234SSC, 991121SDF	100100, 888888, 123456, 121212	1233656
991121QR5, 991121XSX, 991234SSC	100100, 888888, 123456	1112444
991121QR5, 991121XSX, 991234SSC, 991121SDF	100100, 888888, 123456, 121212	1233656

<colgroup><col width="297" style="width: 223pt;"><col width="200" style="width: 150pt;"><col width="64" style="width: 48pt;"></colgroup><tbody>
</tbody>

I want to vlookup each value after comma+space in sheet1 with the table in sheet2 and show the value as in the final output coloumn with comma+space. Also I want to add up all the values after comma+space in the Total coloumn. All this has to repeat till the last comma+space in the series.

 

[oldbrewer]

		991121QR5, 991121XSX, 991234SSC, 991121SDF	991121QR5	991121XSX, 991234SSC, 991121SDF	991121XSX	991234SSC, 991121SDF	991234SSC	991121SDF	991121SDF
			991121QR5	100100	100100
			991121XSX	888888	988988
			991234SSC	123456	1112444
			991121SDF	121212	1233656
			991121SDF	121212	1354868
		CODES	Value
		991121QR5	100100
		991121SDF	121212
		991121XSX	888888
		991234SSC	123456
				total
				1354868
				use helper columns to break the cell up and look up the segments in your code table
				use a running total column and take the MAX value of that column…….
				I will post the formulas if you are interested in this approach

<colgroup><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[codeliftsleep]

This is terrible design. Instead of figuring out a solution for this, I'd advise you to figure out a better way of doing this.

 

[sandeepsandeep]

This is terrible design. Instead of figuring out a solution for this, I'd advise you to figure out a better way of doing this.

Could you please help me out with the solution.

I have a query which I am unable to solve.


I have certain Codes in Sheet1 as below

	A	B	C
1	CODES	OUTPUT	SUM
2	991121QR5, 991121XSX, 991234SSC, 991121SDF
2	991121QR5, 991121XSX, 991234SSC
2	991121QR5, 991121XSX, 991234SSC, 991121SDF

<tbody>
</tbody>

Table in Sheet 2 is as follows:-

	A	B
1	CODES	VALUE
2	991121QR5	100100
3	991121XSX	888888
3	991234SSC	123456
4	991121SDF	121212

<tbody>
</tbody>

The Final Outcome in Sheet 1 should be as follows:-

	A	B	C
1	CODES	OUTPUT	SUM
2	991121QR5, 991121XSX, 991234SSC, 991121SDF	100100, 888888, 123456, 121212	1233656
2	991121QR5, 991121XSX, 991234SSC	100100, 888888, 123456	1112444
2	991121QR5, 991121XSX, 991234SSC, 991121SDF	100100, 888888, 123456, 121212	1233656

<tbody>
</tbody>

How can I get the above OUTCOME using one single formula to vlookup each value from cell A2 after comma+space from sheet1 with the table in sheet2 and place the output in same format as in cell a2 of sheet 1 in cell b2 of sheet 1. Also how to add up all the values of b2 in c2.

 

[codeliftsleep]

Again, you need to stop putting things seperated by a comma in the same cell. That's bad design. Figure out a different way of doing this, then I will help you.

 

[sandeepsandeep]

Let me check if your formula is working for me.