Lookup With Partial Number (1 digits from left, 4 digits from right)

[muhammad susanto]

hi all..
i have table the following below.
i want lookup data using formula with criteria:
1. lookup from 1 digits number from left and 4 digits number from right.
2. length number is always 10 digits.
3. don't use helper column ( formula in single column)
4. not use macro

29_PMK.06_2010PerLampI.XLS
	E	F
3	data	description
4	5020405002	Blackberry
5	5020405003	Mangoes
6	3170106005	Pear
7	3170106006	Tomato
8	2010103000	Potato
9	2010103002	Banana
10
11	lookup	target
12	55002	Blackberry
13	23002	Banana
14	3600	?
15	etc..
Sheet2

any help, thank in advance.
.sst

 

[JoeMo]

Assuming your actual lookup table is expanded to include rows 12 through 100 (change to suit), in F4 and copy down:

=VLOOKUP(VALUE(LEFT(E4,1)&RIGHT(E4,4)),$E$12:$F$100,2,0)

 

[muhammad susanto]

Assuming your actual lookup table is expanded to include rows 12 through 100 (change to suit), in F4 and copy down:

=VLOOKUP(VALUE(LEFT(E4,1)&RIGHT(E4,4)),$E$12:$F$100,2,0)

hi Joe, still not work #N/A

 

[JoeMo]

hi Joe, still not work #N/A

Post the data and the lookup table using XL2BB.

 

[muhammad susanto]

here..

29_PMK.06_2010PerLampI.XLS
	E	F
3	data	description
4	5020405002	Blackberry
5	5020405003	Mangoes
6	3170106005	Pear
7	3170106006	Tomato
8	2010103000	Potato
9	2010103002	Banana
10
11	lookup	target
12	55002	0
13	23002	#N/A
Sheet2

Cell Formulas
Range	Formula
F12:F13	F12	=VLOOKUP(VALUE(LEFT(E4,1)&RIGHT(E4,4)),$E$12:$F$16,2,0)

 

[Michael M]

I don't think you will do this without either duplicating the table with the "NEW" numbers OR creating a helper column....OR of course a VBA solution

 

[muhammad susanto]

I don't think you will do this without either duplicating the table with the "NEW" numbers OR creating a helper column....OR of course a VBA solution

hi Michael, for a reason and for this moment, i don't use helper column & don't use vba too

 

[shaowu459]

If you cannot get expected result like me, apply the formula with Ctrl+Shift+Enter.

Book1.xlsx
	A	B
1	data	description
2	5020405002	Blackberry
3	5020405003	Mangoes
4	3170106005	Pear
5	3170106006	Tomato
6	2010103000	Potato
7	2010103002	Banana
8
9	lookup	target
10	55002	Blackberry
11	23002	Banana
Sheet1

Cell Formulas
Range	Formula
B10:B11	B10	=LOOKUP(1,0/(LEFT(A$2:A$7)&RIGHT(A$2:A$7,4)=A10&""),B$2:B$7)

 

[Michael M]

How about creating the table again similar to your posr

Book1
	E	F
2
3	data	description
4	5020405002	Blackberry
5	5020405003	Mangoes
6	3170106005	Pear
7	3170106006	Tomato
8	2010103000	Potato
9	2010103002	Banana
10
11	lookup	target
12	55002	Blackberry
13	55003	Mangoes
14	36005	Pear
15	36006	Tomato
16	23000	Potato
17	23002	Banana
Sheet1

Cell Formulas
Range	Formula
E12:E17	E12	=VALUE(LEFT(E4,1)&RIGHT(E4,4))
F12:F17	F12	=IF(VALUE(LEFT(E4,1)&RIGHT(E4,4))=VALUE(E12),VLOOKUP(E4,$E$4:$F$9,2,0))

 

[muhammad susanto]

hi guys, thank so much..
Worked it!!!