How do I use logical operators with dynamic arrays?

July 9, 2024

I have the following table where we have the same ID and date. However, I want to create an array that changes the ID based on the ID and the date.

The formula I have tried is:

=IF(AND(DROP(TAKE(A:A,COUNTA(A:A)),1)=12345,
 DROP(TAKE(B:B,COUNTA(A:A)),1)<DATE(2024,1,1)),"54321",
 DROP(TAKE(A:A,COUNTA(A:A)),1))

but record 3 is not giving correct answer

>Solution :

You could try using the following formula:

=IF((A2:A4=12345)*(B2:B4<DATE(2024,1,1)),54321,A2:A4)

Or, can use the following as well, this assumes the ID column as the based for the last row, you don;t need a LAMBDA() helper function here, and instead of using AND() function use the * operator :

=LET(
     _LastRow, MATCH(2,1/(A:A<>"")),
     _ID, DROP(TAKE(A:A,_LastRow),1),
     _Date, DROP(TAKE(B:B,_LastRow),1),
     IF((_ID=12345)*(_Date<DATE(2024,1,1)),54321,_ID))

_{NOTE: Use of * because in Boolean logical operation the rules states that, multiplication corresponds to AND() logical function, while addition + corresponds to OR() logical function.}

Addendum: If you want to use AND() function specifically, you would need LAMBDA() helper functions like MAP():

=LET(
     _LastRow, MATCH(2,1/(A:A<>"")),
     _ID, DROP(TAKE(A:A,_LastRow),1),
     _Date, DROP(TAKE(B:B,_LastRow),1),
     MAP(_ID,_Date,LAMBDA(α,δ,
     IF(AND(α=12345,δ<DATE(2024,1,1)),54321,α))))