Display a list of names if today's date equals a date in any adjacent cell on the same row

Question

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.

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:

=today()

---

Sample