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.