Find duplicates in 2 columns and match values

[alex alex]

Hi,

Have a problem there.
I have values 4 columns.
Column A - new reference number
Column B - Product name
Column C - Product name & old product names
Column D - Old reference numbers
Column E - column for a formula

What is the best way to match products by names and allocate new reference numbers?
Simple sorting by names won't help, because column C  has also old product names.

[#2 · Accepted solution]

Hi Alex,

That could be

=IFERROR(INDEX(A:A,MATCH($C2,B:B,0)),"")

for E2 and drag it down

[#3]

Thank you! Perfectly works!!!!