VLOOKUP on start of cell contents

phunkyfeelone · Oct 10, 2013

Hi, long time listener, first time poster

I have two sets of data, as per example below, and trying to figure out the syntax to perform a lookup based on the first two words in a cell (separated by a comma) in the corresponding data, and return a mapped value.

Master Data

[TABLE="width: 304"]
<tbody>[TR]
[TD][TABLE="width: 300"]
<tbody>[TR]
[TD]Stock[/TD]
[TD]Description[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]12345678[/TD]
[TD]WIDGET,BLUE,STRIPEY[/TD]
[TD]X1[/TD]
[/TR]
[TR]
[TD]12345679[/TD]
[TD]WIDGET,WHITE,SPOTTY[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD]13456802[/TD]
[TD]WIDGET,YELLOW,SPARKLES[/TD]
[TD]X3[/TD]
[/TR]
[TR]
[TD]23498756[/TD]
[TD]WIDGET,BLUE,SPOTTY[/TD]
[TD]X4[/TD]
[/TR]
[TR]
[TD]32165498[/TD]
[TD]WIDGET,GREEN,PLAIN[/TD]
[TD]X5[/TD]
[/TR]
[TR]
[TD]45678912[/TD]
[TD]WIDGET,BLACK,SPOTTY[/TD]
[TD]X6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Lookup Data[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD]Primary[/TD]
[TD]Secondary[/TD]
[TD]Combo[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]WIDGET[/TD]
[TD]BLUE[/TD]
[TD]WIDGET,BLUE[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD]WIDGET[/TD]
[TD]YELLOW[/TD]
[TD]WIDGET,YELLOW[/TD]
[TD]22222222[/TD]
[/TR]
[TR]
[TD]WIDGET[/TD]
[TD]WHITE[/TD]
[TD]WIDGET,WHITE[/TD]
[TD]33333333[/TD]
[/TR]
[TR]
[TD]WIDGET[/TD]
[TD]GREEN[/TD]
[TD]WIDGET,GREEN[/TD]
[TD]44444444[/TD]
[/TR]
[TR]
[TD]WIDGET[/TD]
[TD]BLACK[/TD]
[TD]WIDGET,BLACK[/TD]
[TD]55555555[/TD]
[/TR]
</tbody>[/TABLE]

In effect, I need to look up the first two words in the Master Data, in the Lookup Data (either ColA&ColB or Col C), and return the code to the Master Data.
So for example, the result should look like this:

Master Data (New)
[TABLE="width: 300"]
<tbody>[TR]
[TD]Stock[/TD]
[TD]Description[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]12345678[/TD]
[TD]WIDGET,BLUE,STRIPEY[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD]12345679[/TD]
[TD]WIDGET,WHITE,SPOTTY[/TD]
[TD]33333333[/TD]
[/TR]
[TR]
[TD]13456802[/TD]
[TD]WIDGET,YELLOW,SPARKLES[/TD]
[TD]22222222[/TD]
[/TR]
[TR]
[TD]23498756[/TD]
[TD]WIDGET,BLUE,SPOTTY[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD]32165498[/TD]
[TD]WIDGET,GREEN,PLAIN[/TD]
[TD]44444444[/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts or suggestions?

shadow12345 · Oct 10, 2013

something like this?

<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0">A</td><td align="center" bgcolor="#C0C0C0">B</td><td align="center" bgcolor="#C0C0C0">C</td><td align="center" bgcolor="#C0C0C0">D</td><td align="center" bgcolor="#C0C0C0">E</td><td align="center" bgcolor="#C0C0C0">F</td><td align="center" bgcolor="#C0C0C0">G</td><td align="center" bgcolor="#C0C0C0">H</td><td align="center" bgcolor="#C0C0C0">I</td><td align="center" bgcolor="#C0C0C0">J</td><td align="center" bgcolor="#C0C0C0">K</td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">1</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Stock </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Description </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Code </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Primary </td><td rowspan="1" colspan="1" width="399" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Secondary </td><td rowspan="1" colspan="1" width="435" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Combo </td><td rowspan="1" colspan="1" width="444" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Code </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">2</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">12345678 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLUE,STRIPEY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">X1 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET </td><td rowspan="1" colspan="1" width="399" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">BLUE </td><td rowspan="1" colspan="1" width="435" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLUE </td><td rowspan="1" colspan="1" width="444" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">11111111 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">3</td><td rowspan="1" colspan="1" width="414" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">12345679 </td><td rowspan="1" colspan="1" width="735" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,WHITE,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">X2 </td><td rowspan="1" colspan="1" width="192" height="57" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="57" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="57" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="57" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET </td><td rowspan="1" colspan="1" width="399" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">YELLOW </td><td rowspan="1" colspan="1" width="435" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,YELLOW </td><td rowspan="1" colspan="1" width="444" height="57" align = "left" valign = "top" bgcolor ="#FFFFFF">22222222 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">4</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">13456802 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,YELLOW,SPARKLES </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">X3 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET </td><td rowspan="1" colspan="1" width="399" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WHITE </td><td rowspan="1" colspan="1" width="435" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,WHITE </td><td rowspan="1" colspan="1" width="444" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">33333333 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">5</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">23498756 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLUE,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">X4 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET </td><td rowspan="1" colspan="1" width="399" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">GREEN </td><td rowspan="1" colspan="1" width="435" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,GREEN </td><td rowspan="1" colspan="1" width="444" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">44444444 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">6</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">32165498 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,GREEN,PLAIN </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">X5 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET </td><td rowspan="1" colspan="1" width="399" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">BLACK </td><td rowspan="1" colspan="1" width="435" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLACK </td><td rowspan="1" colspan="1" width="444" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">55555555 </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">7</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">45678912 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLACK,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">X6 </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">8</td><td rowspan="1" colspan="1" width="414" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="735" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="561" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">9</td><td rowspan="1" colspan="1" width="414" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="735" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="561" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">10</td><td rowspan="1" colspan="1" width="414" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="735" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="561" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">11</td><td rowspan="1" colspan="1" width="414" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="735" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="561" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">12</td><td rowspan="1" colspan="1" width="414" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="735" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="561" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">13</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Stock </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Description </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">Code </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">14</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">12345678 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLUE,STRIPEY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B14,1,FIND(",",B14,FIND(",",B14,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">15</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">12345679 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,WHITE,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B15,1,FIND(",",B15,FIND(",",B15,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">16</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">13456802 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,YELLOW,SPARKLES </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B16,1,FIND(",",B16,FIND(",",B16,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">17</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">23498756 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLUE,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B17,1,FIND(",",B17,FIND(",",B17,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">18</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">32165498 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,GREEN,PLAIN </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B18,1,FIND(",",B18,FIND(",",B18,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
<tr><td align = "center" bgcolor="#C0C0C0">19</td><td rowspan="1" colspan="1" width="414" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">45678912 </td><td rowspan="1" colspan="1" width="735" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">WIDGET,BLACK,SPOTTY </td><td rowspan="1" colspan="1" width="561" height="30" align = "left" valign = "top" bgcolor ="#FFFFFF">=VLOOKUP(MID(B19,1,FIND(",",B19,FIND(",",B19,1)+1)-1),$J$1:$K$6,2,FALSE) </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="390" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="399" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="435" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td><td rowspan="1" colspan="1" width="444" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"> </td></tr>
</table>

Aladin Akyurek · Oct 10, 2013

Let Sheet1 house the master data, Sheet2 the lookup data.

Sheet1, C2, just enter, and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$C$2:$C$6,$B2),Sheet2!$D$2:$D$6)

DavidCoop · Oct 10, 2013

I have assumed you want to replace the values X1, X2 etc with the values in the lookup table which I have placed in the range A12:D17.

=VLOOKUP(LEFT(B2,FIND(",",B2,FIND(",",B2,1)+1)-1),$C$13:$D$17,2,0)

This effectively works out where the second coma is to determine the name(s) to look for in the Lookup Data

phunkyfeelone · Oct 11, 2013

Thanks all

shadow12345, works perfectly
Aladin Akyurek, already have data in different worksheets, just simplified it to get the syntax. Not sure about the rest of your solution though!
DavidCoop, also works perfectly, with slightly more efficient code.

Appreciate the quick responses

Aladin Akyurek · Oct 11, 2013

phunkyfeelone said:
...
Aladin Akyurek, already have data in different worksheets, just simplified it to get the syntax. Not sure about the rest of your solution though!
...

How do you mean? Did you map the references correctly?

VLOOKUP on start of cell contents

phunkyfeelone

New Member

Excel Facts

shadow12345

Well-known Member

Aladin Akyurek

MrExcel MVP

DavidCoop

Board Regular

phunkyfeelone

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

VLOOKUP on start of cell contents

phunkyfeelone

New Member

Excel Facts

shadow12345

Well-known Member

Aladin Akyurek

MrExcel MVP

DavidCoop

Board Regular

phunkyfeelone

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock