Lookup Code Not Fully Working

[muhammad susanto]

hi all...

how to make lookup for this table,

rumus lookup vehicle rangka.xlsx
	E	F	G	H	I
3	CODE	YEAR	ID NUMBER (DATA GIVEN)	TARGET-1	TARGET-2
4	A	1980	MHFM1BA3JAK016162	1980	2010
5	A	2010
6	1	2001	MHFF1VA4K6K016162	?	?
7	1	2031
8	6	2002
9	6	2032
Sheet1 (2)

Cell Formulas
Range	Formula
H4:I5	H4	=IFERROR(IF(LEN(G4)=4,G4+30,VLOOKUP(MID(G4,10,1),$E$4:$F$9,2,0)),"")

for code 1 the formula not working..
Id Number in pulled from number of 10th from left
any body help me..

.sst

 

[Dave Patton]

G information yields text; consequently, lookup E4:E9 should be text.
Your formula requires that E6:E9 be text.
Delete the numbers, format the area as text and then enter 1, 1, 6, 6 You could use VLOOKUP(MID(G6,10,1)+0,$E$4:$F$9,2,0) but then this work not work with text like A

 

[muhammad susanto]

hi Dave still not work...the result is #value!

 

[Dave Patton]

T202007a.xlsm
	E	F	G	H	I
3	CODE	YEAR	ID NUMBER (DATA GIVEN)	TARGET-1	TARGET-2
4	A	1980	MHFM1BA3JAK016162	1980	2010
5	A	2010
6	1	2001	MHFF1VA4K6K016162	2002	2032
7	1	2031
8	6	2002
9	6	2032
1d

Cell Formulas
Range	Formula
H4:I6	H4	=IFERROR(IF(LEN(G4)=4,G4+30,VLOOKUP(MID(G4,10,1),$E$4:$F$9,2,0)),"")

What part of your formula is not working?
What did you try?
Did you use Excel's Formulas Evaluate Formula?

 

[muhammad susanto]

T202007a.xlsm
	E	F	G	H	I
3	CODE	YEAR	ID NUMBER (DATA GIVEN)	TARGET-1	TARGET-2
4	A	1980	MHFM1BA3JAK016162	1980	2010
5	A	2010
6	1	2001	MHFF1VA4K6K016162	2002	2032
7	1	2031
8	6	2002
9	6	2032
1d

Cell Formulas
Range	Formula
H4:I6	H4	=IFERROR(IF(LEN(G4)=4,G4+30,VLOOKUP(MID(G4,10,1),$E$4:$F$9,2,0)),"")

What part of your formula is not working?
What did you try?
Did you use Excel's Formulas Evaluate Formula?

hi Dave...
thank you so much!! Working perfect.

 

[Dave Patton]

Thanks for the feedback.