Duplicate value for lookup

Occasional Visitor

I have a set of data with the stock number and its' specification and another set of data with the order and the requested specification, is there a way to use excel to automatically match the stock to the order without duplicating stock for different orders?

 

Here's a sample of the dataset. One with the stock number and the specification:

 

 

Stock IDSpecification
212345144PRMP
212346144PRMP
212347123ELTP
212348123ELTP
212349144BASI
212350144BASI
212351177BASI
212352177BASI
212353123BASI
212354123BASI

 

The other order ID and the specification:

 

Order IDSpecification
A123144PRMP
A124144PRMP
A125144PRMP
A126144PRMP
A127177BASI
A128177BASI
A129123ELTP
A130123BASI

 

Result I'm trying to achieve:


Order IDStock IDSpecification
A123212345144PRMP
A124212346144PRMP
A125N/A144PRMP
A126N/A144PRMP
A127212351177BASI
A128212352177BASI
A129212347123ELTP
A130212353123BASI
1 Reply

Hi @marcolo2012! I have a solution for you. In my suggestion you need two auxiliary columns, but you can hide them in a separate worksheet or otherwise. I have included a sample document so that it will work in your regional Excel environment. If you can't use XLOOKUP() yet, I have suggested two alternatives. I hope the solution meets your expectations. Otherwise please contact me again.