Lookup table with inconsistent number of leading zeros

I have a lookup table in which the lookup column is composed of product codes. The codes are inconsistent in length, and contain inconsistent numbers of leading zeros. There is a second list of product codes which is used as lookup values, to compare with the first table. The second list also contains inconsistent numbers of leading zeros, and does not necessarily match the syntax of the first list.
How can I find a match between the two product code columns, when the number of zeros does not match? I have tried the VALUE function, but that seems to work only on the lookup value - I cannot get it to work on the lookup table.

Use this formula to convert the various forms to "0001" form. Paste this in D3 and copy down to see results:

Please Login or Register  to view this content.

SUBSTITUTE strips off the hyphens, then TEXT formats what's left to 4 digits, including leading zeros.

Paste this in G4 and copy down. (You also need to format the cells as currency):

Please Login or Register  to view this content.

This strips the hyphens AND the leading zeros, leaving numbers for the lookup (faster than text). These formulas demonstrate the general principles. For anything more involved, we'd have to see a complete sampling of your actual data.