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

Stock	Description	Code
12345678	WIDGET,BLUE,STRIPEY	X1
12345679	WIDGET,WHITE,SPOTTY	X2
13456802	WIDGET,YELLOW,SPARKLES	X3
23498756	WIDGET,BLUE,SPOTTY	X4
32165498	WIDGET,GREEN,PLAIN	X5
45678912	WIDGET,BLACK,SPOTTY	X6

Lookup Data

Primary	Secondary	Combo	Code
WIDGET	BLUE	WIDGET,BLUE	11111111
WIDGET	YELLOW	WIDGET,YELLOW	22222222
WIDGET	WHITE	WIDGET,WHITE	33333333
WIDGET	GREEN	WIDGET,GREEN	44444444
WIDGET	BLACK	WIDGET,BLACK	55555555

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)

Stock	Description	Code
12345678	WIDGET,BLUE,STRIPEY	11111111
12345679	WIDGET,WHITE,SPOTTY	33333333
13456802	WIDGET,YELLOW,SPARKLES	22222222
23498756	WIDGET,BLUE,SPOTTY	11111111
32165498	WIDGET,GREEN,PLAIN	44444444

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