Match unsorted Excel data from two columns (products) to assign value to a third column (prices)

Question

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

30X-BTA,$8.00,DCX-BTD,$9

Your help will be appreciated

Answer 1

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

Answer 2

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.

https://defeatexcel.com/vlookup-vs-index-match/