how to copy old price data onto new stock list if match exists?

J

julan

I have an old price list which i want to use to populate the new price list.
The new stock list has some items the same - some are missing, some new
items are added. How can i copy the price field for the items on the old
stock list that match the new list? I cant figure out where to look on help.
The lists are in excel.
 
F

Fred

Julian,

Use VLOOKUP() to find a the old price for those items that previously
exisited.

Assume: Old price list has Part # in ColA, Price in ColE
Sort old price list by Part #

Assume: New price list has Part # in ColA, Price in ColG
In new price list G2 enter
=if(iserror(vlookup(A2,'Old List'!A1:E999,5,FALSE),"",vlookup(A2,'Old
List'!A1:E999,5,FALSE)))
where "'Old List'!A1:E999" is the name of the sheet and range containing the
old prices.
This will return the price if Part No exists otherwise NULL
Copy down all rows in new list - Job done.

HTH
 
G

greg7468

Hi Julian,
you can use VLOOKUP to do this.

Say your old list items are in A1:A100
and their prices are in B1:B100

The items in column A must be in alphabetical order

if your new list of items are in D1:D100
in E1 put this formula
=VLOOKUP(D1,$A$1:$B$100,2,FALSE)

This will look for the value in D1 in the table of the old stock
A1:B100
If it finds a match it will return the value found in column B and put
it in column E.

If it does not find that item in the old list it will retun a #N/A.

You can then drag this down for all your values in column D.

HTH.
 
Top