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

  • Welcome to Web Applications. Please show what you tried and add a brief description of you search efforts as is suggested in How to Ask. – Rubén Mar 26 at 19:57
  • @Rubén Could I ask you to reopen this question that has been corrected by @Tedinoz? Thank you (Rubén and Tedinoz) very much for your help. – odyn1982 Apr 3 at 7:10
  • Done c.c @Tedinoz – Rubén Apr 3 at 15:45

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 at 23:29
  • I was right. I changed the locale to US and it worked :) Thank you @Tedinoz :) – odyn1982 Apr 3 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 at 6:40

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.