Converting equation to be used for ArrayFormula results in weird values

user_9205480 0
Viewed 65

I have the following (pretty ugly) equation for column Y:

=IF(ISTEXT(Q2),"NO VIDEO DATA",IF(REGEXMATCH(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$G$1)-COLUMN(DEALS!$A$1)+1,FALSE), "Likes"), IF(P2>=(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$F$1)-COLUMN(DEALS!$A$1)+1,FALSE)), IF((R2<> W2),IF(R2,CONCATENATE("DUE: ", CONCATENATE("$",M2)),IF(W2,CONCATENATE("DUE: ", CONCATENATE("$",L2)),)),IF((R2* W2)>0,"COMPLETED",CONCATENATE("DUE: ", CONCATENATE("$",Z2)))),IF(today() >= N2+K2,IF(X2,"PAID: MUST REPOST","NO PAYMENT: MUST REPOST"),IF(today() < N2+K2,CONCATENATE((N2+K2)-today()," days remaining"),))), IF(Q2>=(VLOOKUP(I2,DEALS_RNG,COLUMN(DEALS!$F$1)-COLUMN(DEALS!$A$1)+1,FALSE)), IF((R2<> W2),IF(R2,CONCATENATE("DUE: ", CONCATENATE("$",M2)),IF(W2,CONCATENATE("DUE: ", CONCATENATE("$",L2)),)),IF((R2* W2)>0,"COMPLETED",CONCATENATE("DUE: ", CONCATENATE("$",Z2)))),IF(today() >= N2+K2,IF(X2,"PAID: MUST REPOST","NO PAYMENT: MUST REPOST"),IF(today() < N2+K2,CONCATENATE((N2+K2)-today()," days remaining"),)))))

Not getting too into the full logic going on here, basically as it is now, it is functioning correctly when I drag it down rows:

equation being used in column Y

I changed my AND() to * and XOR() to <> as well as my cells from i.e. Q2 to Q2:Q, etc. But once in ArrayFormula the formula no longer works correctly and returns weird numbers (memory perhaps? idk):

formula used in arrayformula

Could anyone help me understand what is going on and how I could possibly resolve this?

Appreciate any help I can get!

nested-if array-formulas google-sheets if-statement arrays
1 Answers

try:

=ARRAYFORMULA(IFERROR(IF(ISTEXT(Q2:Q), "NO VIDEO DATA",
 IF(REGEXMATCH(VLOOKUP(I2:I, DEALS_RNG, 7, ), "Likes"), 
 IF(P2:P>=(VLOOKUP(I2:I, DEALS_RNG, 6, )), 
 IF(R2:R<>W2:W, 
 IF(R2:R, "DUE: $"&M2:M,
 IF(W2:W, "DUE: $"&L2:L, )),
 IF((R2:R*W2:W)>0, "COMPLETED", "DUE: $"&Z2:Z)),
 IF(TODAY()>=(N2:N+K2:K), 
 IF(X2:X, "PAID: MUST REPOST", "NO PAYMENT: MUST REPOST"),
 IF(TODAY()<(N2:N+K2:K), (N2:N+K2:K)-TODAY()&" days remaining", ))), 
 IF(Q2:Q>=VLOOKUP(I2:I, DEALS_RNG, 6, ), 
 IF(R2:R<>W2:W, 
 IF(R2:R, "DUE: $"&M2:M,
 IF(W2:W, "DUE: $"&L2:L, )),
 IF((R2:R*W2:W)>0, "COMPLETED", "DUE: $"&Z2:Z)),
 IF(TODAY()>=(N2:N+K2:K),
 IF(X2:X, "PAID: MUST REPOST", "NO PAYMENT: MUST REPOST"),
 IF(TODAY()<(N2:N+K2:K), (N2:N+K2:K)-TODAY()&" days remaining", )))))))
user_56326290
Related