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

S

sandeepsandeep

New Member
Joined
Dec 21, 2017
Messages
10
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Would you be able to deconstruct the data on a separate tab using text to columns, do individual vlookups, and the concatenate the answers?
 
Upvote 0
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?
 
Upvote 0
Sheet1 would look like following
[TABLE="width: 440"]
<colgroup><col width="297" style="width: 223pt;"><col width="97" style="width: 73pt;"><col width="46" style="width: 35pt;"></colgroup><tbody>[TR]
[TD="class: xl678021, width: 297, align: center"]A[/TD]
[TD="class: xl748021, width: 97, align: center"]B[/TD]
[TD="class: xl758021, width: 46, align: center"]C[/TD]
[/TR]
[TR]
[TD="class: xl688021, align: center"]CODES[/TD]
[TD="class: xl658021, align: center"]FINAL OUTPUT[/TD]
[TD="class: xl768021, align: center"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl708021"]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD="class: xl668021"] [/TD]
[TD="class: xl698021"] [/TD]
[/TR]
[TR]
[TD="class: xl708021"]991121QR5, 991121XSX, 991234SSC[/TD]
[TD="class: xl668021"] [/TD]
[TD="class: xl698021"] [/TD]
[/TR]
[TR]
[TD="class: xl718021"]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD="class: xl738021"] [/TD]
[TD="class: xl728021"] [/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 has following Table
[TABLE="width: 142"]
<colgroup><col width="87" style="width: 65pt;"><col width="55" style="width: 41pt;"></colgroup><tbody>[TR]
[TD="class: xl6514607, width: 87, align: center"]A[/TD]
[TD="class: xl6714607, width: 55, align: center"]B[/TD]
[/TR]
[TR]
[TD="class: xl6614607, align: center"]CODES[/TD]
[TD="class: xl6814607, align: center"]Value[/TD]
[/TR]
[TR]
[TD="class: xl6914607"]991121QR5 [/TD]
[TD="class: xl7014607, align: right"]100100[/TD]
[/TR]
[TR]
[TD="class: xl6914607"]991121SDF [/TD]
[TD="class: xl7014607, align: right"]121212[/TD]
[/TR]
[TR]
[TD="class: xl6914607"]991234SSC [/TD]
[TD="class: xl7014607, align: right"]123456[/TD]
[/TR]
[TR]
[TD="class: xl7114607"]991121XSX [/TD]
[TD="class: xl7214607, align: right"]888888[/TD]
[/TR]
</tbody>[/TABLE]

I want Final Output as foll
[TABLE="width: 561"]
<colgroup><col width="297" style="width: 223pt;"><col width="200" style="width: 150pt;"><col width="64" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl653753, width: 297, align: center"]A[/TD]
[TD="class: xl663753, width: 200, align: center"]B[/TD]
[TD="class: xl673753, width: 64, align: center"]C[/TD]
[/TR]
[TR]
[TD="class: xl683753, align: center"]CODES[/TD]
[TD="class: xl693753, align: center"]FINAL OUTPUT[/TD]
[TD="class: xl703753, align: center"]TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl713753"]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD="class: xl723753"]100100, 888888, 123456, 121212[/TD]
[TD="class: xl733753, align: right"]1233656[/TD]
[/TR]
[TR]
[TD="class: xl713753"]991121QR5, 991121XSX, 991234SSC[/TD]
[TD="class: xl723753"]100100, 888888, 123456[/TD]
[TD="class: xl733753, align: right"]1112444[/TD]
[/TR]
[TR]
[TD="class: xl743753"]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD="class: xl753753"]100100, 888888, 123456, 121212[/TD]
[TD="class: xl763753, align: right"]1233656[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
[TABLE="width: 1135"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD]991121QR5[/TD]
[TD]991121XSX, 991234SSC, 991121SDF[/TD]
[TD]991121XSX[/TD]
[TD]991234SSC, 991121SDF[/TD]
[TD]991234SSC[/TD]
[TD]991121SDF[/TD]
[TD]991121SDF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]991121QR5[/TD]
[TD="align: right"]100100[/TD]
[TD="align: right"]100100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]991121XSX[/TD]
[TD="align: right"]888888[/TD]
[TD="align: right"]988988[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]991234SSC[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1112444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]991121SDF[/TD]
[TD="align: right"]121212[/TD]
[TD="align: right"]1233656[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]991121SDF[/TD]
[TD="align: right"]121212[/TD]
[TD="align: right"]1354868[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CODES[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]991121QR5[/TD]
[TD]100100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]991121SDF[/TD]
[TD]121212[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]991121XSX[/TD]
[TD]888888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]991234SSC[/TD]
[TD]123456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1354868[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]use helper columns to break the cell up and look up the segments in your code table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]use a running total column and take the MAX value of that column…….[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]I will post the formulas if you are interested in this approach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
codeliftsleep said:
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.
Click to expand...

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


[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CODES[/TD]
[TD]OUTPUT[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table in Sheet 2 is as follows:-
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CODES[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5[/TD]
[TD]100100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]991121XSX[/TD]
[TD]888888[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]991234SSC[/TD]
[TD]123456[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]991121SDF[/TD]
[TD]121212[/TD]
[/TR]
</tbody>[/TABLE]


The Final Outcome in Sheet 1 should be as follows:-
[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CODES[/TD]
[TD]OUTPUT[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD]100100, 888888, 123456, 121212[/TD]
[TD]1233656[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC[/TD]
[TD]100100, 888888, 123456[/TD]
[TD]1112444[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]991121QR5, 991121XSX, 991234SSC, 991121SDF[/TD]
[TD]100100, 888888, 123456, 121212[/TD]
[TD]1233656[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Look if values exist seperately and together, then write Yes or No in cell
2
Replies
11
Views
776
MasterBash
M
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
842
Gajendran Yadhav
G
J
  • Solved
vlookup in a cell with comma separated values returned in another comma separated value
Replies
6
Views
12K
stevemcmillen
S
S
  • Question Question
Count each time each value occurs in a comma-delimited list
Replies
1
Views
614
Z51
Z
L
  • Solved
Copy last number from comma seperated cell
Replies
6
Views
275
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top