Lookup a value which is contained in a cell and return a value

[jimmy13]

Hi Guys,

I have a problem I cant find a solution too.

I have a table of stock codes with corresponding price list (exported from Sage), but on another sheet I would like ot lookup an abbreviated code and return the corresponding price. This means searching cells which contain a particular 4 digit number. I have attached a screen shot of what I am after.

Can anyone help?

Many thanks

Jim

 

[anandvarma]

Dear jimmy13,

that screen shot is not visible.

 

[jimmy13]

Sorry, try this http://flic.kr/p/bXPofY

 

[anandvarma]

What is relation between the Product Description and Product code?

 

[jimmy13]

Basically the product codes are loacted within the product description as a 4 digit number. for example:
Product Code 1356 is related to cell A4 "0.3mm Olivine AFS30 in 25kg bags - 1356"

 

[TheCman81]

Basically the product codes are loacted within the product description as a 4 digit number. for example:
Product Code 1356 is related to cell A4 "0.3mm Olivine AFS30 in 25kg bags - 1356"

Hi Jimmy, Is the 4 digit product code always at the end of the product description?

 

[anandvarma]

Dear jimmy13,

try this formula
=INDEX($B$2:$B$16,MATCH(TRUE,ISNUMBER(SEARCH(E2,$A$2:$A$16)),0)) followed by Control+Shift+Enter
and drag it down

anand varma

 

[jimmy13]

That is awesome. Thanks.

What does the ctrl+shift+enter do?

 

[anandvarma]

Dear jimmy13,

I am glad it helped you

thanks for the feedback,

Control+Shift+Enter is tells the Excel it is an array formula.
Since we have included the search function in the match formula.
anand varma