0

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

1
  • Welcome to Web Applications. The links has denied the access, anyway, while including a link to a external resource, questions on this site should be self contained, meaning that all the relevant details including some sample data and the corresponding expected result, should be included directly on the question body. Ref. How to Ask.
    – Rubén
    May 30 at 23:42

2 Answers 2

2

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.

7
  • Wow, my assumption that it was super easy and I was overlooking something simple was just flat wrong!! Thank you so much for the help!!!! Jun 1 at 1:53
  • Believe it or not, Joshua, this solution isn't terribly complex as formulas go, though I it is likely outside the realm of normal spreadsheet users. The principles here become sort of back-of-the-hand knowledge when you do it all the time. In fact, this free, volunteer-run forum generally isn't the place to come for actual customized, complex, time-intensive solutions (people would generally need to hire someone for that kind of work). In this case, I'm glad it felt fancy (ha ha) and solved your issue.
    – Erik Tyler
    Jun 1 at 3:16
  • The formula will give an accurate result but it is quite slow because of all the string manipulation. Use a query smush if you have more than a couple hundred rows of data. Note that the sample spreadsheet contains data below row 100 as well. Jun 1 at 8:54
  • @doubleunary, I write only to posted details. The OP has current part data through Row 27 with an indicated max of 100. I didn't experience lag running the formula. It seems you may be upset about this answer having been marked as "Best Answer." I can only encourage you not to get too wrapped up in that. It's an endless rabbit hole. You do good work. Let that be enough.
    – Erik Tyler
    Jun 1 at 13:19
  • @JoshuaBirmingham, doubleunary is correct in noting that you have superfluous data (formulas) in Row 101. I suggest you clean up the sheet by selecting Row 101 of your Database sheet and hitting Delete to get rid of any formulas running across that row.
    – Erik Tyler
    Jun 1 at 13:21
1

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 
) )
2
  • 1
    This one looked simpler, but it's returning some false info, and it appears to my novice eyes that it would only search column 2 where there's sometimes parts in column 3,4, etc. Erik's solution works so far with all the things I've tried, but I hugely appreciate the help you gave as well!! Jun 1 at 1:58
  • The formula will search all columns. There are three values below row 100 which may explain the "false info" you mention. Edited the formula so that it uses space separators to get an exact match instead of a substring match. Jun 1 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.