IF AND OR Nested ArrayFormula not working - What am I missing?

Question

I am trying to set the status of the last column ("Status") to show "Boosted", "Vaccinated", "Religious Exemption" using an ArrayFormula so I don't have to add the formula every time I add an employee to the list.

={"Covid Status";
ARRAYFORMULA (
IF (
    A2:A = "Religious Exemption" ,
         "Religious Exemption" ,
IF (
    AND ( OR ( A2:A = "Moderna" , A2:A = "Pfizer" ) ,
         AND ( ISDATE ( B2:B ) = TRUE , ISDATE ( C2:C ) = TRUE , ISDATE ( D2:D ) = TRUE ) ) ,
               "Boosted" ,
IF (
    AND ( OR ( A2:A = "Moderna" , A2:A = "Pfizer" ) ,
          AND ( ISDATE ( B2:B ) = TRUE, ISDATE ( C2:C ) = TRUE , ISDATE ( D2:D ) = FALSE ) ) ,
               "Vaccinated",
    )
    )
    )
    )
}

I wouldn't mind adding "Unvaccinated" to the list either, but blank is sufficient.

If there's an easier way to achieve the results, I'm way open to options. The "last" column in the sheet is the desired result.

I've used the formula without being in an array, but it doesn't work as an array.

I hope I've shared it correctly.

https://docs.google.com/spreadsheets/d/1LIpe0uo6RQtbcAUcB6V5jrGaX-9wuSm-2vTU5Vj03Cs/edit?usp=sharing

Answer 1

Instead of AND(a,b) use a*b and instead of OR(a,b) use a+b and instead of ISDATE use ISDATE_STRICT.

={"Covid Status";
ARRAYFORMULA (
IF (
    A2:A = "Religious Exemption" ,
         "Religious Exemption" ,
IF (
     (  ( A2:A = "Moderna" ) + ( A2:A = "Pfizer" ) ) *
         ( ISDATE_STRICT ( B2:B ) * ISDATE_STRICT ( C2:C ) * ISDATE_STRICT ( D2:D ) )  ,
               "Boosted" ,
IF (
    ( ( A2:A = "Moderna") + (A2:A = "Pfizer" ) ) *
           ( ISDATE_STRICT ( B2:B ) * ISDATE_STRICT ( C2:C ) * ( 1-ISDATE_STRICT ( D2:D )) )  ,
               "Vaccinated",
    )
    )
    )
    )
}

You could also use the MAP function.

={"Covid Status";
MAP(A2:A,B2:B,C2:C,D2:D,LAMBDA(a,b,c,d,IF (
    a = "Religious Exemption" ,
         "Religious Exemption" ,
IF (
    AND ( OR ( a = "Moderna" , a = "Pfizer" ) ,
         AND ( ISDATE ( b ) , ISDATE ( c) , ISDATE ( d) ) ) ,
               "Boosted" ,
IF (
    AND ( OR ( a = "Moderna" , a = "Pfizer" ) ,
          AND ( ISDATE ( b ) , ISDATE ( c ) , 1-ISDATE ( d ) ) ) ,
               "Vaccinated",
    )
    )
    )
    ))
}

Answer 2

You can also try this solution, which consists of three parts:

=ArrayFormula(LET(covVax,A2:A12,
                  dateList,--ISDATE_STRICT(B2:D12),
                  dateCond,QUERY(dateList,"SELECT Col1*Col2,Col1*Col2*Col3 LABEL Col1*Col2 '', Col1*Col2*Col3 ''",0),
                  status,IFS(covVax="Religious Exemption","Religious Exemption",
                             (covVax<>"")*CHOOSECOLS(dateCond,2),"Boosted",
                             (covVax<>"")*CHOOSECOLS(dateCond,1),"Vaccinated",
                             TRUE,),
                  finalAns,IFERROR(VSTACK("Covid Status",status),),
                  finalAns))

Here's the breakdown:

First, we need to check the dates in one go using ISDATE_STRICT and convert the results to numeric using "--" :

2nd, We use the result above to calculation the date conditions for Vacinated and Boosted statuses by using the QUERY Function. This allows as to perform the AND Multiplication within an array:

Finally,we use the IFS Function to determine the status. We also add the header using the VSTACK Function at the last part:

Note: We use the CHOOSECOLS Function to select the right column (date condition).