Data search, match and replace

[leanneo81]

Hi, Not sure how best to explain this but I've recently implemented a new POS system in my retail shop which uploads product data in an excel spreadsheet with name, description, pricing, etc. I've just been sent a new price list by a supplier and I'd like to update our pricing in our POS system without having to re-sort, re-arrange the products and variants again. Hoping for some advice on how best to search for a match in data (the SKU number) and then replace an associated value (wholesale price) with a new value if there is a match between the SKU in the old and new spreadsheets. We've done a lot of manipulation of the data to get it in the format we need to upload it and I don't want to have to go thru that process each time there is a price change.

NOTE there are over 5000 rows of data. I just need to replace the old wholesale price with the new one and the old retail price with the new one based on a match with the value in the SKU column. 

Sorry I can't attach a file I don't think as it would be too large.

Thanks in advance for your help!

[#2]

@leanneo81 

You should be able to do this with the VLOOKUP formula with the lookup based on the SKU.

=VLOOKUP(Your Sheet SKU, Supplier Sheet Range Starting With SKU Col, Relative Col Number in Range Containing Price)

[#3]

@leanneo81 

VLOOKUP() or INDEX/MATCH or like will work, but you'll have both old and new price if only partial substitution is needed.

Better with Power Query both files and generate third clean one.

If you provide sample files with only few records in each it will be easier to demonstrate on formulas.