Find and replace numbers in separare columns

QuestionEdit

QUESTION: I have anproblem which I'm sure there is a quick and easy way of doing - but I just don't know! I use2007.

I have twofiles. First is a master file which has detailed information about products (3000 entries). Second is a file with new prices to add to the master file (1500 entries).

I need to add the new prices to the original spreadsheet, however only 1500 have new prices.

Both files have products listed by number and name (in different columns).

Is there a simple way of doing this? Or do I need to do it manually.

Thank you so much for your help : )

ANSWER: You can use a formula for this.

Next to the "old" table, start typing:

=IFERROR(VLOOKUP(

Then point at the cell with the current "ID" you get something like:

=IFERROR(VLOOKUP(Table1[[#This Row];[Id]] ENter a comma and hit control+tab to go to the other table. Select the entire table. Your formula now should look like:

=IFERROR(VLOOKUP(Table1[[#This Row];[Id]],Table2.xlsm!Table1[#All]

Add this to the formula (assuming the price is the second column in the table):

=IFERROR(VLOOKUP(Table1[[#This Row];[Id]],Table2.xlsm!Table1[#All],2,FALSE),

Control+tab back to the previous file and click on the cell that shows the CURRENT price and add a closing ). your formula should be similar to:

=IFERROR(VLOOKUP(Table1[[#This Row];[Id]],Table2.xlsm!Table1[#All],2,FALSE),Table13[[#This Row];[Price]])

The result should be a column with existing prices for the ones not in the new table and new prices for the ones in the new table.

COpy this column and paste-special values on top of the old price column once you're convinced all is well.

---------- FOLLOW-UP ----------

QUESTION: I'm sorry - I'm not sure if I am being dense. I've opened a newfile, and pasted copies of the original and new databases into one file. I've made sure that the product code is column one and price is column two in both sheets.

I've then tried following your instructions but it gives me this

=IFERROR(VLOOKUP(original!A1,new!A1465:B1573,2,FALSE),original!B1)

Which gives me an error message in the cell.

Then if I try to change the code you've given to fit in my with my cells like this

=IFERROR(VLOOKUP(original[[C];[3]],new[B1:C1573],2,FALSE),original[[C];][1])

It also throws up an message.

Am I missing something?

Thank you,

Cait (who is an editor by trade, so is mostly confused by numbers!).

AnswerEdit

I assumed the tables were "formatted as table". They obviously are not, hence the normal A1 syntaxt result in your formula. In that casse, your first formula should work, but you probably need to "fix" the reference to the new table:

=IFERROR(VLOOKUP(original!A1,new!$A$1:$B$1573,2,FALSE),original!B1)

Make sure you edit the $A$1:$B$1573 so your entire new table is included.