return text based on a value in =Today() column header

[florin119]

Hello! I have an automated attendance sheet. I need a formula when if an employee is on duty "D" in the {=Today()} column, the employee name should appear in the duty table. Thank you!

[ORoos]

Hi there,

Please refer to the attached file.

We can use a combination of Index/Match where we use the DAY to offset the columns.
The formula below will return the first matching result. By changing the very last '1' to a '2' we get the second match for the "D".
This is an array formula, hence is entered by pressing Ctrl + Shift + Enter keys simultaneously.

Please Login or Register  to view this content.

Cheers..

[CARACALLA]

AJ11=FILTER($B$7:$B$25,INDEX($C$7:$AG$25,,MATCH(AJ$6,$C6:$AG6,0))="D")

Copy across

[florin119]

Thank you ORoos for your reply! It works !

[florin119]

Thank you CARACALLA for your reply! It works !

[florin119]

Is there a way to apply this formula --- AJ11=FILTER($B$7:$B$25,INDEX($C$7:$AG$25,,MATCH(AJ$6,$C6:$AG6,0))="D") --- in MS Office 2007? I have an old workstation that I can't update to MS Office 365. Thank you!

[CARACALLA]

AJ11=IFERROR(INDEX($B$7:$B$25,SMALL(IF($C$7:$AG$25="D",IF($C$6:$AG$6=AJ$6,ROW($B$7:$B$25)-ROW($B$7)+1)),ROWS($B$7:B7))),"")

Control+shift+enter

copy across and down

[florin119]

Thank you for your expertise! I appreciate your effort and time! Best wishes!