Search code examples
google-sheets-formula

Searching/Filtering/Finding in Google Sheets

So, I'm trying to create a search feature for my church's rota spreadsheet. I want it to eventually look as below, but haven't worked out what formulas would work for it...An example of how I want the search feature to look when complete, showing a list of dates and activities for each of the three service types for the searched name.

My church has three different services it operates, 10.30am, Praise, and then irregular services. I have separated out the activities for each service because I have one sheet for each service, instead of them all being on the same sheet. Therefore I figured it would be easier to split it out this way instead of doing something like this: An example of what I'd consider to be a more advanced version of this search feature, with three columns showing dates, service types and activities. However, if this would actually be easier than what I envisioned above, feel free to tell me.

The reason this is slightly more complicated than it was already going to be is that I have the data I want to search in three separate worksheets: A screenshot of 4 different worksheets. The search feature would go into the "Information" sheet, as this is the landing page for the rota, whereas the data to be searched is in all of the other 3.

Now, the reason I don't believe FILTER would work is that this rota is shaped like a traditional table, with headers along the top and left hand side of the table, and the data in the middle. As far as my understanding reaches, FILTER works when you want to search the headers and bring back the data attached to the header, whereas I want to do it the other way around. I want to search the data and bring back the headers attached to that data. Here is one of the three tables I want to draw data from, names blanked out for privacy, showing the headers and data location.

Does anyone know anything that could help me with this?

I have done multiple Google searches and read numerous articles on the FILTER, SEARCH, FIND and MATCH functions respectively, but believe none of them to be applicable in this situation. SEARCH, FIND and MATCH I have discarded because they seem to return a number representing where in a string the item was found, and FILTER I have discarded because, while it does bring back data from the cells around matches, and is capable of bringing back multiple matches, you can only search the first row or column.

Solution

  • You may try:

    =let(Σ,reduce(wraprows(,3,),{"10.30am";"Praise";"Irregular"},lambda(a,c,{a;
       index(iferror(split(tocol(if(indirect("'"&c&"'!B2:CZ5")=I4,indirect("'"&c&"'!B1:CZ1")&"|"&c&"|"&indirect("'"&c&"'!A2:A5"),),1),"|"),wraprows(,3,)))})),
     query(Σ,"Where Col3!='' order by Col1 format Col1 'ddd, dd mmmm yyy'"))
    • first and foremost for this formula to work w/out issues, please allocate equal dose of columns in all 3 tabs i.e., till CZ (right now 10.30am extends till CF; Irregular till Z...)
    • Your A2:A5 values seem to be right only in Irregular tab while the other 2 tabs A2:A5 is slightly varying; which you may need to check upon

    enter image description here