I am attempting to make a spreadsheet in Google Sheets that will protect the "database" from those able to access it, so I've got the first sheet as the "front end." On the second sheet I've got in A1 through ME1 the names of inventory locations, and under that in separate cells going down any part number found in those locations. I want someone to be able to type into cell D5 on Sheet 1 any part number, and Google Sheet return the value of the column header into D7. I've found lots of ways to return values under the headers, and I've found ways of returning the value of the headers if a value of a row equals something else, but I'm having a lot of trouble just getting the Location. It seems so much more simple than the questions my Google searches are returning, like I'm missing something obvious.
For example, say someone wants to know where part number SVC10001 is, so they type it into Sheet 1 in cell D5 (where prompted to), and after hitting enter it would say that part's location (RACK 1 A 2) in D7. While it's unlikely that part number SVC10001 would be in another location also, it's possible, so I might have list every location it's found in. The location name is always in row 1, but the part number could be listed anywhere lower than 2 (to a max I have set currently of 100).