Problem with Lookup text and numbers

Hello,
I use the following formula, in Excel 2003, to lookup our product codes from a column:- =(LOOKUP(C7,Products!$A$3:$A$624,Products!$B$3:$B$624))*F7

Most codes are 2 letters followed by 4 numbers, e.g. CH0104, no problem with this. However, some codes also have an additional letter at the end, e.g. CH0104L, in many (not all) instances adding a letter to the end returns an incorrect result. Has anyone experienced similar and know of any way around this?

Thanks in advance.

Originally Posted by VBA Noob

It you want to return the first 6 characters try

Left(C7,6)

VBA Noob

Thanks for your reply.
To clarify, Column A is a list of product codes, column B a list of the corresponding weights for each product. When a product code has a letter at the end of the code it is returning a value (weight) for a completely different product. It appears to be getting confused by the additional letter.

The list of information to be looked through must be sorted in ascending order, otherwise errors will occur, either as #N/A or incorrect results.

is this the case?

Originally Posted by arthurbr

The list of information to be looked through must be sorted in ascending order, otherwise errors will occur, either as #N/A or incorrect results.

is this the case?

Thank you. First 6 characters were sorted but where a letter appeared at the end these were not always. e.g. CH0104S preceded CH0104L. All working fine now, thanks again.