How to return header value if in the column it contains typed value

Question

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).

https://docs.google.com/spreadsheets/d/1eqSDqBAYs9LjaSCOqwLBFiV8Lc_4Y9TvXXYU6VuS8ZA/edit?usp=sharing

Answer 1

I suggest this:

=ArrayFormula(IFERROR(QUERY(SPLIT(FLATTEN(Database!1:1&"~"&TRIM(LOWER(Database!2:100))),"~",1,0),"Select Col1 WHERE Col2 = '"&TRIM(LOWER(D5))&"'",0),"NO MATCH"))

Database!1:1&"~"&TRIM(LOWER(Database!2:100)) will concatenate every header in the Database sheet with every part listed in that column, placing a tilde ~ between the two as a temporary delineator. TRIM and LOWER make sure that spaces or capitalization in either the listings or the search don't trip up the system.

FLATTEN will turn the above into a one-column array.

SPLIT will split each entry at the tilde to form a two-column array.

QUERY will return only those entries where Col2 matches the TRIM and LOWER version of what is typed in D5.

IFERROR will return "NO MATCH" if an error occurs (either if what is typed in D5 is not valid or if such a part is not listed in the Database sheet).

The formula only asks to consider the first 100 rows of the Database sheet. If you need more, just change 2:100 to, say, 2:125, etc.

This formula should return accurate results quickly.

Answer 2

Use the query smush pattern and use string matching get locations, like this:

=arrayformula( query( 
  transpose( 
    { 
      Database!A1:ZZ1; 
      " " & query(Database!A2:ZZ, "", 9^9) & " " 
    } 
  ), 
  "select Col1 where Col2 contains ' " & trim(D5) & " '", 
  0 
) )