1

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful.

I have a sheet like this:

Source Data

in row 1 dates and below songs used that Sunday.

As a result, I need the table like this, where I can see when the song was used last time:

enter image description here

MATCH works good with one column, but how can I do it with multi-column range?

I've also tried to join columns to strings:

=join(",",A2:A7)

enter image description here

And then use QUERY:

=query(A$12:B$15,"select max(A) where B like '%"&F1&"%' label max(A) ''")

And it works, but not automatically...

  • You could nest a where clause inside a query which pulls up certain dates, you could also have a static list of dates and use a vlookup? Do you have a sample sheet? – Datanovice Sep 22 '18 at 20:27
0

I created a sample sheet from your data, with a few more columns.

Basically, I took raw data, and added another row immediately underneath the date header. This new row contains =JOIN(";",A4:A9), and is copied to each cell in the row.

To produce the desired output, I simply did an HLOOKUP on the dates and the joined data (with a SORT to put it in the correct order). Here is the formula I used:

=HLOOKUP("*"&D12&"*",TRANSPOSE(SORT(TRANSPOSE($A$2:$G$3),2,FALSE)),2,FALSE)

D12 contains "Song1" in this case. A2:G3 contains the dates and the songs joined together.

I'm sure there are better methods to accomplish what you want, especially ones which don't require copying multiple formulas to a lot of cells. I just did this as a quick proof of concept.

  • Please mark the answer as correct if it solved your problem, or else state why it didn't. – Kwilinsi Oct 2 '18 at 21:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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