Forum Discussion
Duplicate value for lookup
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 |
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.
