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:


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?
    – Umar.H
    Sep 22, 2018 at 20:27
  • webapps.stackexchange.com/a/146781/256097 allows for a query using column headers.
    – Sid
    Oct 11, 2020 at 5:38

1 Answer 1


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:


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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