V-look ups only work on unique identifiers - you know that
You can do what you need to do - but it will involve setting it up properly.
What you need to use is "H-Lookup"
Rather than looking across the columsn you look at the rows - which means you can select the rows you want - hence why you have to set the spreadsheet up in advance
See attached it may well explain better
In the data tab - this is where you need to store the information - so you will need to set it up this way
In the Hlookup tab - you have to build the formulas across the columns as I have done - with vlook up you select the column number, in this formula you set the row number, and you just make sure that formula is set up across the spreadsheet properly
In my example if you change the product id to "Box 2" - it will change the part numbers as you require
I hope this helps