Hi there i want to be able to extract the data from column C in sheet2 and replace it onto column C in sheet 1 , but only if column A (car reg) matches in both sheets
for instance the price of a car in sheet 1 need to change to the price in sheet 2, i have got a sheet at work that has thousands of rows , how can i do this quickly.
Please help
Put this formula in D2 of Sheet1:
=IFERROR(VLOOKUP(A2,Sheet2!A:C,3,0),C2)
and copy down to the bottom of your data. Then fix the values in column D - to do this select column D by clicking on the column identifier, then click <copy>, then right-click and select Paste Special from the drop-down, then click on Values | OK and then press the <Esc> key.
Then you can delete column C of Sheet1 (unless you wish to retain the previous values).
Hope this helps.
Pete
pete this came back with the correct prices , but i also gives me a price for A16 (dd58htx) in sheet 1, which is not in sheet 2
it should only update if the car reg is in both sheets, the car reg could also be in different row numbers in column A
Last edited by dougers1; 10-22-2015 at 02:57 PM.
The reason it gave you that price is because the formula Pete gave you returns the value in the matching cell in sheet1 (in this case cell C16) in the event there is an error (#N/A result).
Maybe this will work better for you...
=IFERROR(IF(VLOOKUP(A2,Sheet2!A:C,3,FALSE)=VLOOKUP(A2,Sheet2!A:C,3,FALSE),VLOOKUP(A2,Sheet2!A:C,3,FALSE)),"no price available")
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
this one BTW is probably cleaner and I like better... =IFERROR(INDEX(Sheet2!C:C,MATCH(Sheet1!A2,Sheet2!A:A,0)),"not on sheet2") copied down.
I would have done the same as Pete. If the registrations match, it returns the price from sheet2. If it doesn't, it returns the existing price from Sheet1, column C.
My take:Formula:
Please Login or Register to view this content.
If that's not what, you want then:Formula:Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Sorry, just to add, as Pete explained, the advantage of his formula is that you get a complete column of values, new and original. You can then convert that to values and delete the original column C if you wish.
Regards, TMS
Thanks Guy's this has helped me big time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks