Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulanested-iftextjoin

How to search a row for cell value(s) then output the header?

I'm new here and trying to automate. I have a work roster and would like for it to output who are on duty on a daily basis. Ideally. it would check today's date then search the corresponding table row for the relevant personnel each day.

Screenshot

Spreadsheet: here

Desired output:

On today's date, AM shifts are Person 1 (duty) Person 2, PM shifts are Person 3

Current formula:

="On "&textjoin("",TRUE,B7)&": AM shifts are "
&OFFSET(INDEX(A3:E3,MATCH("AM Duty",A3:E3,0)),1-row(vlookup(today(),A1:E5,2,0)),0)&" (duty) "
&OFFSET(INDEX(A3:E3,MATCH("AM Reg",A3:E3,0)),1-row(vlookup(today(),A1:E5,2,0)),0)
&", PM shifts are "
&OFFSET(INDEX(A3:E3,MATCH("PM Reg",A3:E3,0)),1-row(vlookup(today(),A1:E5,2,0)),0)

Some problems with formula:

  1. Row needs to adjust according to today's date as it goes down the list, currently it's hardcoded A3:E3
  2. Unsure how to capture repeated AM Reg in each row

Not sure if I'm overcomplicating things here, and open to better solutions. Thank you in advance!

Solution

  • try:

    =INDEX(TEXT(TODAY(), "On dd mmmm yy: A\M \s\hift\s ar\e ")&
 TEXTJOIN(", ", 1, IF(REGEXMATCH(VLOOKUP(TODAY(), A2:E5, {2,3,4,5}, ), "AM Duty"), B1:E1&" (duty), ", ))&
 TEXTJOIN(", ", 1, IF(REGEXMATCH(VLOOKUP(TODAY(), A2:E5, {2,3,4,5}, ), "AM Reg"),  B1:E1, ))&" and PM shifts are "&
 TEXTJOIN(", ", 1, IF(REGEXMATCH(VLOOKUP(TODAY(), A2:E5, {2,3,4,5}, ), "PM Duty"), B1:E1&" (duty), ", ))&
 TEXTJOIN(", ", 1, IF(REGEXMATCH(VLOOKUP(TODAY(), A2:E5, {2,3,4,5}, ), "PM Reg"),  B1:E1, )))

    enter image description here