VLOOKUP Error

[sfink22]

Hello, having one heck of a time with a seemingly routine VLOOKUP. For some reason, I cannot get this to work - I've tried formatting as general, number, text, with no luck. Naming a range, using a locked range instead, nothing. I keep getting a #N/A "value not available" error.

Here's the setup:

	A	B	C	D	E
1	Serial Number	Model Year		Serial Start	Year
2	9110253 <tbody> </tbody>	*where vlookup goes, should return 2011		909	2009
3	9094299 <tbody> </tbody>	*where vlookup goes, should return 2009		911	2011

<tbody>
</tbody>

First column is serial number, second is model year. My reference table is a list of Serial Start numbers with corresponding Years. I want my VLOOKUP to look at the serial number, match the first three digits to the Serial Start column, and return the year.

Here is an example of what I currently have in the cell (which is returning the above error):

=VLOOKUP(A2,D$2$:E$3$,2,TRUE)

Absolutely 100% sure all values are in the table, so this error is baffling me. Any help would be appreciated!

 

[Michael M]

Maybe this

=VLOOKUP(LEFT(A2,3)+0,$D$2:$E$4,2,0)

 

[sfink22]

THANK YOU!! It worked perfectly. Only an hour of my time wasted and solved in 5 minutes

 

[Michael M]

Well, the VLOOKUP didn't specify you wanted the left 3 characters in A2....
AND

the dollar signs in the table range were in the wrong place..in future if you want to make a range absolute, either select the range OR type the range in the formula THEN press F4 to make the range absolute

 

[sfink22]

Ahh well - I didn't copy paste my code so I accidentally put them in the wrong spot the kicker was specifying the left three characters. Couldn't find that anywhere in a google search!