I have been struggling with an excel IF statement with multiple parameters and am hoping someone can help.
I am putting together a table to demonstrate whether a test subject is currently taking part in a study.
I have two tabs: Test Subjects & Study Information.
The Test Subjects tab is laid out as such:
Test Subject ID | Study ID
The Study Information is laid out as such:
Study ID | Study Start Date | Study End Date
If the Study ID on the Test Subjects tab is equal to the Study ID in the Study information tab, and today's date falls between the Study Start Date and Study End Date, return "Yes". If not, return "No".
The array formulas I have tried only work if the Study ID on the Test Subjects tab is the first one in the list on the Study Information tab. If I switch it to any other Study IDs, I get a false even if the result should be true.
Failed Formula Examples:
L5 is the Study ID on the Test Subjects tab.
'Study Information'!$B$7:$B$71 is the Study ID range that I am trying to match with.
'Study Information'!$C$7:$C$71
`Study Information'!$D$7:$D$71` is the study end date.
=IF((L5='Study Information'!$B$7:$B$71)*(TODAY()>='Study Information'!$C$7:$C$71)*(TODAY()<='Study Information'!$D$7:$D$71),"Yes","No")
Study IDs are alpha numeric. I am not sure if that makes any difference. I'd appreciate any feedback.
Thanks in advance.
