Partial vlookup help

I have a list of product codes and the number of characters vary. I'm trying to lookup a product from my product list to a partial part number, returning the partial part number. The first list is my product list and the second list is the partial part list. The difficulty in this is that the partial part list isn't nessarily the first few characters of the product, some are the first few characters and some are characters in the middle of the list. Does anyone know if there's away to look these up?

Product Codes
USX2065-JZX-TR
LAN8710A-EZC-TR
SEC1300-JZX-01G1
USX2064-AEZG-TR
USB5434-3086JZX-TR


Partial Part #
X2065
LAN8710
SEC1300
2064
3086JZ

It's dangerous, but have your tried the SEARCH function?

LOOKUP(lookup_value,lookup_vector,result_vector) has 3 parameters separated by commas (so 2^15,1 is actually 2^15 and the 1 is part of the second parameter)

2^15 is just the lookup_value (it's just a large number like Jeff's 9.99E+307)

1/(ISNUMBER(SEARCH($A$9:$A$13,A6))) produces a lookup_vector that produces an array of errors except the one successful searched item

$A$9:$A$13 is the result_vector

Hope this helps...