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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Serial Number[/TD]
[TD]Model Year[/TD]
[TD][/TD]
[TD]Serial Start[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl64, width: 90"]9110253[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]*where vlookup goes, should return 2011[/TD]
[TD][/TD]
[TD]909[/TD]
[TD]2009[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl66, width: 90"]9094299[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]*where vlookup goes, should return 2009[/TD]
[TD][/TD]
[TD]911[/TD]
[TD]2011[/TD]
[/TR]
</tbody>[/TABLE]

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!