searching a Part code, gives a list of piece numbers which provides it's w/h location

Welcome to the forum

Each VLOOKUP can only return one piece of information. Moreover, VLOOKUP is not great at searching in a one to many context (such as in your case where you have one Part Code with many piece numbers and one piece in many locations). If Excel had to store this data you will need to duplicate each part code multiple times to link it to each piece number, etc. The moment you do this, VLOOKUP bombs out as it is designed to search for unique values, find a match and return info about that item. With duplicates, it will find the first instance and never return data for the others.

Of course you can attempt to overcome this by concatenating the Part Code and Piece No. but this complicates the process esp. since you have multiple locations for each Piece too...

A better tool for the task might be a Pivot Table that groups data together. If you can set up a table with Part Code, Piece No., Location, and other columns, a pivot can consolidate all the data into a easy to read report and provide additional summary info with aggregate formulas and filtering. not sure if this is what you are after...but it's an option that can assist to find information quicker by groups.

The ultimate solution for you would be a simple relational database that can cope with one-to-many relationships and provide queries and forms that can be used to locate and maintain data.