I'm trying to find a column value return similar to the function the MATCH
function performs for rows.

I have a large spreadsheet with part numbers for a product in row 1, column
headers under each part number and the 6 columns to the right and parts data
from rows ~3-~1500.

product-partnumber
1 2 3 4 5 6 7 (COLUMN headers)
A B C D E F G (data rows 3-1500)


I have dozens of part numbers for products which each have a parts list
under them so I need to be able to find the column the product-part number is
in, search down the column for the reference designator of the part and then
right 1 column in the same row for the part number of a failed component.