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]

101007	y	{=OFFSET(A3,MATCH(--LEFT(A1,4),--LEFT(A4:A7,4),0),1)}
		use crtl + **** + enter
100901	a	try this.
100902	s
101001	y
101002	h

<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> </tbody>

 

[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"

SKU</SPAN>	MOL_ORIG</SPAN>	MOL_NEW</SPAN>
100900</SPAN>	3.5</SPAN>	2.5</SPAN>
100901</SPAN>	3.5</SPAN>	2.5</SPAN>
100902</SPAN>	3.5</SPAN>	2.5</SPAN>
100903</SPAN>	3.5</SPAN>	2.5</SPAN>
100100</SPAN>	4</SPAN>	5</SPAN>
150066</SPAN>	2</SPAN>	7</SPAN>
160055</SPAN>	3</SPAN>	9</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Tab 2: "MSTR_SKU"

SKU</SPAN>	MOL</SPAN>
1009</SPAN>	2.5</SPAN>
1001</SPAN>	5</SPAN>
1500</SPAN>	7</SPAN>
1600</SPAN>	9</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

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"

SKU</SPAN>	MOL_ORIG</SPAN>	MOL_NEW</SPAN>
100900</SPAN>	3.5</SPAN>	2.5</SPAN>
100901</SPAN>	3.5</SPAN>	2.5</SPAN>
100902</SPAN>	3.5</SPAN>	2.5</SPAN>
100903</SPAN>	3.5</SPAN>	2.5</SPAN>
100100</SPAN>	4</SPAN>	5</SPAN>
150066</SPAN>	2</SPAN>	7</SPAN>
160055</SPAN>	3</SPAN>	9</SPAN>

<TBODY>
</TBODY>

Tab 2: "MSTR_SKU"

SKU</SPAN>	MOL</SPAN>
1009</SPAN>	2.5</SPAN>
1001</SPAN>	5</SPAN>
1500</SPAN>	7</SPAN>
1600</SPAN>	9</SPAN>

<TBODY>
</TBODY>

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)