Lookup on array that is adjusted within formula

[AlbertSmith]

I do not want to add new columns to an existing table - However I do want to do a lookup based on just the year from a date column that is formatted as YYYYMM. 

I attempted to put a "LEFT(cell, 4)" function in the lookup without any success.

[#2]

LEFT will pull the first 4 digits of the date's serial number in that example. You'd have to use YEAR instead.