Lookup function w/Text and Year

[Josh O.]

I am trying to create a formula to lookup a product number and return the
product description. Some of the items numbers are comprised of product
number and date. For example, item 9601 is for "Book Name 2001" and 9602 is
for "Book Name 2002."

Is there a way to make a formula lookup the 4 digit product number and if
the lookup array only contains 2 digits use the last two digits of product
number to fill in the year on the product description.

For example:
Lookup Array:
Item Number: 96__ Item description: Book Name 20__
Item Number: 97__ Item description: Item 2 Name 20__
Item Number: 5655 Item description: Item 3 Name

Spreadsheet 1:
Lookup Value 1: 9605 Result: Book Name 2005
Lookup Value 2: 9704 Result: Item 2 Name 2004
Lookup Value 3: 5655 Result: Item 3 Name

Any Ideas would be appreciated?

 

[nbrcrunch]

You can use a secondary field as your true look that examines the user's
input and if it string only has two digits at the end, then insert "20"
in front of it. However, your formula logic looks like it is going to
get "hairy" seeing as it also has to determine whether or not to use
"19" or "20"

Essentially, use the AND operator (ampersand) to concatenate the
string

In plain English, the formula would be

If the 3rd to the last character in the input string is not numeric,
then
the NewLookUpValue equals the OriginalInputString up and including the
3rd to the last character AND "20" AND the last two characters of the
OriginalInputString.

=IF(right(OriginalInput,3)>9,mid(OriginalInput,1,len(OriginalInput)-3)&"20"&right(OriginalInput,3),OriginalInput)