My spreadsheet has "variant" names in Column B, and relevant dates in adjacent columns C-H on the same row. I have included a screenshot below

I want to create formula that will display a list "variant" names when "todays's date" (today()) matches a date in the adjacent Columns C-H on the same row as the "variant" name. The list would be displayed in Column I.

For example:

  • If today's date is, say, 26 March 2021 then (based on the data in the screenshot below) the list of "variants" would be those five names circled in red.

  • When today's date is 27 March 2021, the list of variants would automatically update to include only those five names with a value of 2021-03-27 (in the same row as the name) in one of the adjacent Columns C-H.

Normally some kind of vlookup/hlookup/lookup might do the trick, but this has to evaluate 6 date values and return the "variant" name from a column to the left of the date columns. Maybe a query or filter might be better since they could also apply to any new rows of data.

my Google Sheet


1 Answer 1


It is likely that your list can be generated in several ways (and probably more efficiently than the following answer). Please consider this as one alternative. I hope that I have not misunderstood your requirements; if so, then please let me know.

Insert this formula in Cell I3 (based on the sample data shown below):

=query({B3:H7},"select Col1 where Col2 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col3 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col4 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col5 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col6 = date '"&text(B1,"yyyy-MM-dd")&"' OR Col7 = date '"&text(B1,"yyyy-MM-dd")&"'")

Note: cell B1 contains this formula:



Sample data

  • Thank you @Tedinoz. I tried exactly as you wrote but still got a formula error. Maybe the local language settings have something to do with this because I have Polish in the system (I'm guessing). Can you try the formula in my duplicate sheet? docs.google.com/spreadsheets/d/…
    – odyn1982
    Apr 3, 2021 at 23:29
  • I was right. I changed the locale to US and it worked :) Thank you @Tedinoz :)
    – odyn1982
    Apr 3, 2021 at 23:40
  • This recent answer addresses the issue of comma vs semi-colon argument separators. Btw, if the answer was useful, then you might consider accepting it
    – Tedinoz
    Apr 7, 2021 at 6:40

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.