Duplicate value for lookup

[marcolo2012]

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 ID	Specification
212345	144PRMP
212346	144PRMP
212347	123ELTP
212348	123ELTP
212349	144BASI
212350	144BASI
212351	177BASI
212352	177BASI
212353	123BASI
212354	123BASI

The other order ID and the specification:

Order ID	Specification
A123	144PRMP
A124	144PRMP
A125	144PRMP
A126	144PRMP
A127	177BASI
A128	177BASI
A129	123ELTP
A130	123BASI

Result I'm trying to achieve:

Order ID	Stock ID	Specification
A123	212345	144PRMP
A124	212346	144PRMP
A125	N/A	144PRMP
A126	N/A	144PRMP
A127	212351	177BASI
A128	212352	177BASI
A129	212347	123ELTP
A130	212353	123BASI

[#2]

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.