VLOOKUP or Find-and-Replace a column with comma delimited text

J

joshbjames

New Member
Joined
Jul 25, 2017
Messages
33
I have a list of comma delimited text that I would like to VLOOKUP the values of, preferably without using Text-to-Columns. I would like to use VLOOKUP so that if there is an error I can return "Error".

Sheet 1 is my lookup list.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NUMBER[/TD]
[TD]NAME[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]C-1032-4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]C-1032-4 Hendrickson C10324 Trailer Axle Radius Arm - Adjustable T-Rod[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]0029141[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]0029141 Hendrickson 002914-1 Tie Rod End - LH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]0029142[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="width: 463"]0029142 Hendrickson 002914-2 Pusher Axle Tie Rod End - RH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]002890[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]002890 Hendrickson R-002890 Pusher Axle Tie Rod Assembly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]004239-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]004239-2 Hendrickson R0042392 Fender Bracket[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]004239-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]004239-1 Hendrickson R0042391 Fender Bracket[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]422[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]422 McNeilus 00422 Vacuum Gauge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]82393[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]82393 McNeilus 0082393 Flapper Pin for 0000470 Flopper Assembly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]82397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]82397 McNeilus 0082397 Water Tank Flapper Seal for 0000470 Flopper[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]82399[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]82399 McNeilus 0082399 Water Tank Flapper Spring Set[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]19013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 463"]
<tbody>[TR]
[TD="class: xl65, width: 463"]19013 Washer, Split Lock, 1/4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The second sheet is my data and where Id like to do the calculation.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATA[/TD]
[TD]CALCULATION/ RESULT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]C-1032-4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]C-1032-4 Hendrickson C10324 Trailer Axle Radius Arm - Adjustable T-Rod[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]0029141,0029142[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]0029141 Hendrickson 002914-1 Tie Rod End - LH,0029142 Hendrickson 002914-2 Pusher Axle Tie Rod End - RH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]002890,0029142[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]002890 Hendrickson R-002890 Pusher Axle Tie Rod Assembly,0029142 Hendrickson 002914-2 Pusher Axle Tie Rod End - RH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]0029141,002890[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]0029141 Hendrickson 002914-1 Tie Rod End - LH,002890 Hendrickson R-002890 Pusher Axle Tie Rod Assembly[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]004239-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]004239-2 Hendrickson R0042392 Fender Bracket[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]004239-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]004239-1 Hendrickson R0042391 Fender Bracket[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]0108555,422[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ERROR,422 McNeilus 00422 Vacuum Gauge[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]82393,82396,82397,82398,82399,200620,19013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]82393 McNeilus 0082393 Flapper Pin for 0000470 Flopper Assembly,ERROR,82397 McNeilus 0082397 Water Tank Flapper Seal for 0000470 Flopper,ERROR,82399 McNeilus 0082399 Water Tank Flapper Spring Set,ERROR,19013 Washer, Split Lock, 1/4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 290"]
<tbody>[TR]
[TD="class: xl65, width: 290"]0000702[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ERROR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance, this has been a thinker for me!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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