How to search partial text string using INDEX?

Hi,

i am trying search product weights based on there product codes but since there is two catergories for every product code, i am trying to identify which section the search should be in based on the ist 2 letters of the product description, how it appears not to be working.

Any help will be great.

Sorry for the confusion. There are two production areas (raw material and cooked), each with 3 production lines. As same finished product code is used in both sections, i am trying to get the forumla to look in the cooked section if the description of the material says CK (CK for cooked) at the start, or else in the raw section.

I have attached an amended report which shows the working i am expecting and the values given by the formula for cooked product highlighted in red as they are incorrect. It seems the formula is requiring there to be also be same product being produced in the raw section for it to show a result and it is then using the data from the 2nd product in the list. Whether it is in the cooked or raw section does not seem to affect it. I need both this factors to not exist, basically for the formula to completely ignore the raw section and use data from the cooked section.

Could it be something to do with the range within which it looks in, that is incorrect?


Hope this helps Clarify things.

JeteMc,

I have tried your suggestion but without luck as i can not really change the layout of the production Plan. Instead i have tried adding "RAW" and "COOKED" in column 1 but even that does not help, see attached sheet.

Also forgot to mention in my original post i am also trying to get it to work where "CK" is not the first 2 letters but in the 4th or 5th letter, as you will see in the sheet.

Thanks JeteMc, that has done the job.