Update price list - please help!

I've been suffering in silence for a week, trying to figure this out by myself, but the more I try and fix, the more I'm getting confused. Please help!

This is what I have:

Sheet 1 (Master Price List with 11000 part numbers)
Column A: Part number
Column B: Price (New Prices)

Sheet 2 (Stock list of 900 part numbers with OLD prices)
Column A: Part number (in some cases containing 2 or more part numbers - OLD and NEW)
Column B: Price (Old Prices)

I need to pull the new prices from Sheet 1, Column B and insert into Sheet 2, Column B

I have used the following formular to fulfill this task:
=VLOOKUP(A2,Sheet1!A2:B10825,2,) which has been partly successful.

The problem is that many part numbers in Sheet 2, Column A, contain 2 part numbers in a cell an OLD number and a NEW number (which is in Sheet 1, Column A).

I can't work out how to set up the formula so that it checks partial data from Sheet 2, Column A to match the relevant part number in Sheet 1, Column A.

For example in Sheet 2, Column A, I have 2 part numbers in one cell: "1111111 = 2222222" but of course excel can't extract 2222222 from one cell to match the relevant cell with 2222222 in Sheet 1, Column A.

I apologise if this is long winded, I just want to make sure you understand my problem from the outset!

Thanks in advance.

Try this, using right() in the formula will compare to the right hand number of characters specified (I have specified 7 as per your example)

=VLOOKUP(A2,RIGHT(SHEET1!A2:B10825,7),2,0)

I like Special-K's Method for finding the correct length of string You can incorporate that into your VLOOKUP like this.

=VLOOKUP(A2,RIGHT(SHEET1!A2:B10825,LEN(A1)-FIND("=",A1)-1) ),2,0)

I wasn't thinking about the possibility of different length strings. I manage a very large pricing system and all of our part numbers are 9 characters long.

I like Special-K's Method for finding the correct length of string You can incorporate that into your VLOOKUP like this.

=VLOOKUP(A2,RIGHT(SHEET1!A2:B10825,LEN(A1)-FIND("=",A1)-1 ),2,0)

Oops, One too many ")" in the previous example.

Thank you all for answering so quickly! I should point out that despite using excel for more years than I can remember, it's at times like this I realise that I am next to clueless! I've been trying your various suggestions, but without any luck! I'm going to attach a sample of the file, and hope this helps....

Here is a small sample of the file in question....

Sorry - previous file had no formula included. This one has the formula I've been using.

OK I just copied cell B2 from your edited sheet, and pasted the formula to B2 in my original sample sheet. Everything works, as in your version.
BUT, when I try the same in my master file, and drag the formula down the column, I get a mixture of #VALUE! and #N/A. What am I doing wrong?

N.B. Some parts numbers have letters in them - I don't know if this has an influence on the errors I'm getting.

Well at least you made an idiot laugh!

In some instances, a part number has been changed over the years up to 6-7 times, so if I understand you correctly, my stock list can't link up with the new price in the master list, if it's in position 3? (e.g. 111111 = 22222 = 333333) - in this case part number 333333 won't be updated - is that right?

I suppose I could try and separate old and new into different cells - there would be a lot of manual work, as I would have to check which numbers are old and which are new, and then extract and sort into the required column....

If you could PM your email address that would be really appreciated.