Need help with cross reference of parts from more than one source

CROSS REFERENCE.xlsxI have 2 sheets of data that I would like anyone to be able to use to find a series of replacement parts for the one they currently have.
On the first sheet I have the most current part number in column A1 to A242, columns B to R have part numbers in them that include the ones from column A(these are in column B1-B242). The rest of the columns have part numbers for newest to oldest part number used and not all cells are used due to some of the items being new and only cross referencing to column B and other have be cross reference up to 16 or 17 times to get to the newest in column A. The first part of my question would be how could I query the whole table from one cell on sheet 3 and have a column of data appear in order from the row of data on the first sheet on the same sheet(3). the next part would be to have the answer(being the most current part number) to also show on sheet 3 the parts required to make it work. This information would be on sheet 2 and would also have the sheet 1 column A information in the same position but beside these would be 10 more columns with suggested add-on items. If any one can figure this out I would really appreciate that, I am try to show a couple of examples of what I would like the final sheet to look like.


Attachment 293360


Thank you for taking time to even look at this.