Using VLookup with Left Function

[kelbrod]

I have two tabs, one containing an id with 6 numbers (i.e. 100901, 100902, 101001, 101002, etc.). I need to lookup only the first 4 numbers (1009, 1010) to obtain a value from another table matching only the first 4 numbers. I am trying to use a formula with both the Vlookup and the Left functions.
=VLOOKUP(LEFT(A2,4),SKU_X,2) ...not working. Help!!!

 

[vogel997]

[TABLE="width: 464"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="64" style="width: 48pt;"> <col width="448" style="width: 336pt; mso-width-source: userset; mso-width-alt: 16384;"> <tbody>[TR]
[TD="width: 107, bgcolor: transparent, align: right"]101007[/TD]
[TD="width: 64, bgcolor: transparent"]y[/TD]
[TD="width: 448, bgcolor: transparent"]{=OFFSET(A3,MATCH(--LEFT(A1,4),--LEFT(A4:A7,4),0),1)}[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]use crtl + **** + enter[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100901[/TD]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]try this.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100902[/TD]
[TD="bgcolor: transparent"]s[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]101001[/TD]
[TD="bgcolor: transparent"]y[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]101002[/TD]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[kelbrod]

Thank you for replying, but the formula sent is not working. Below are the details of my spreadsheet. Any help is appreciated.

Tab 1: "SKU"
[TABLE="width: 213"]
<TBODY>[TR]
[TD]SKU</SPAN>[/TD]
[TD]MOL_ORIG</SPAN>[/TD]
[TD]MOL_NEW</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]100900</SPAN>[/TD]
[TD="align: right"]3.5</SPAN>[/TD]
[TD="align: right"]2.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]100901</SPAN>[/TD]
[TD="align: right"]3.5</SPAN>[/TD]
[TD="align: right"]2.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]100902</SPAN>[/TD]
[TD="align: right"]3.5</SPAN>[/TD]
[TD="align: right"]2.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]100903</SPAN>[/TD]
[TD="align: right"]3.5</SPAN>[/TD]
[TD="align: right"]2.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]100100</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]150066</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]160055</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
Tab 2: "MSTR_SKU"

[TABLE="width: 72"]
<TBODY>[TR]
[TD]SKU</SPAN>[/TD]
[TD]MOL</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1009</SPAN>[/TD]
[TD="align: right"]2.5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1001</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1500</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1600</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

I can't get the formula to work when I combine the left function with Vlookup. Maybe there is another way?

 

[Gilliam]

the left function turns your 100901 numbers into text. to combat this, do
=VLOOKUP(VALUE(LEFT(A2,4)),SKU_X,2)
to fix it

 

[Brian from Maui]

In addition to coercing the LEFT function to return a number to match your SKU, by omitting the 4th argument, VLOOKUP defaults to True(1),so your lookup column must be sorted. You can add FALSE (0) for a exact match if your lookup column is not sorted.

 

[Aladin Akyurek]

Thank you for replying, but the formula sent is not working. Below are the details of my spreadsheet. Any help is appreciated.

Tab 1: "SKU"
[TABLE="width: 213"]
<TBODY>[TR]
[TD]SKU</SPAN>
[/TD]
[TD]MOL_ORIG</SPAN>
[/TD]
[TD]MOL_NEW</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]100900</SPAN>
[/TD]
[TD="align: right"]3.5</SPAN>
[/TD]
[TD="align: right"]2.5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]100901</SPAN>
[/TD]
[TD="align: right"]3.5</SPAN>
[/TD]
[TD="align: right"]2.5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]100902</SPAN>
[/TD]
[TD="align: right"]3.5</SPAN>
[/TD]
[TD="align: right"]2.5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]100903</SPAN>
[/TD]
[TD="align: right"]3.5</SPAN>
[/TD]
[TD="align: right"]2.5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]100100</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]150066</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]160055</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]9</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
Tab 2: "MSTR_SKU"

[TABLE="width: 72"]
<TBODY>[TR]
[TD]SKU</SPAN>
[/TD]
[TD]MOL</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1009</SPAN>
[/TD]
[TD="align: right"]2.5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1001</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1500</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1600</SPAN>
[/TD]
[TD="align: right"]9</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

I can't get the formula to work when I combine the left function with Vlookup. Maybe there is another way?

Sheet1, C2, copied down:

=LOOKUP(9.99999999999999E+307,FIND("|"&Sheet2!$A$2:$A$5,"|"&A2),Sheet2!$B$2:$B$5)