Combine vlookup with left function

[RAJESH1960]

Hi guys,
I have used a help line with this formula =LEFT(D2,2). I had to use paste special in the help column to get the reult. I am trying to combine the two formulas in one but I am getting an error. Need your expertise to combine these 2 formulas to one without the help line.

Get left data.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		ID No	xx	Formula to get result
2	06	06XTXXK9823D1ZK		F											01	A
3	06	06XTXXK9823D1ZK		F											02	B
4	09	09BTFXK0365M1ZB		I											03	C
5	07	07BDCXS2940D1ZX		G											04	D
6	09	09XXJFD5413B1ZR		I											05	E
7	03	03XXHFH0769G1ZH		C											06	F
8	03	033XCWXJ7850R1ZK		C											07	G
9	03	06XTXXK9823D1ZK		C											08	H
10	03	06XTXXK9823D1ZK		C											09	I
11	03	09BTFXK0365M1ZB		C
12	03	07BDCXS2940D1ZX		C
13	03	09XXJFD5413B1ZR		C
14	03	03XXHFH0769G1ZH		C
15	03	033XCWXJ7850R1ZK		C
Sheet1

Cell Formulas
Range	Formula
F2:F15	F2	=VLOOKUP(C2,$Q$2:$R$10,2,0)

 

[johnny51981]

Try
=VLOOKUP(LEFT(D2),$Q$2:$R$10,2,0)

 

[RAJESH1960]

=VLOOKUP(LEFT(D2),$Q$2:$R$10,2,0)

I am getting an error

 

[johnny51981]

Oh, I see. It is a difference between having Values in one side and Text on the other.

Try this:
=VLOOKUP(VALUE(MID(D2,2,1)),P2:Q10,2,FALSE)

 

[Fluff]

Try

=VLOOKUP(Left(D2,2)+0,$Q$2:$R$10,2,0)

 

[RAJESH1960]

Try

=VLOOKUP(Left(D2,2)+0,$Q$2:$R$10,2,0)

Fluff. Got it right. Thanks

 

[RAJESH1960]

Oh, I see. It is a difference between having Values in one side and Text on the other.

Try this:
=VLOOKUP(VALUE(MID(D2,2,1)),P2:Q10,2,FALSE)

JohnnyS. This formula is right with a few correction. It is columns Q and R and you need to freeze it like this $Q$2:$R$10. Then you will get the right answer. Thanks for your help.

 

[Fluff]

Fluff. Got it right. Thanks

You're welcome & thanks for the feedback.