I have four columns: Column A has a part number, column B has a new price Column C has a part number, column D has an old price.

Column A and C are not identical nor do they have the same number of rows (column B has more rows as it includes obsolete part numbers) while not identical the columns have many identical part numbers.

Columns C and D have more entries than Columns A and B as they include obsolete products.

I want to match the new price from Column A to its corresponding part number in column C and place the new price in column E.

I need to keep the existing (sort) order in columns C as I have to import column C and E into an existing spreadsheet which has an row order that cannot be changed.

Sample of unmatched data columns (comma separated)

Part-code, New-Sell, Part-code(web), Old-sell


Your help will be appreciated

  • @Axel Kemper Do you want to post that as an answer? – barry houdini Aug 31 '13 at 15:05

Excel's VLOOKUP function might be helpful here.

It can be used to lookup data using a key or an ID.

Link: http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx


Assuming this is your column layout: http://prntscr.com/nv818v

  • Add this formula to E2 if you want to see N/A: =VLOOKUP(C2,$A$2:$B121,2,FALSE)

  • Add this formula to E2 if you dont want NA visible: =IF(ISNA(VLOOKUP(C2,$A$2:$B121,2,FALSE)),"",VLOOKUP(C2,$A$2:$B121,2,FALSE))

  • Adjust the "$B121" to the length of your data

VLookup is simpler, but consider replacing for "Index-Match" if your excel skills are there as it will give you better options if the spreadsheet evolves.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.