CODEWITHSUNDEEP
google-sheets
Larry Berrill
Larry Berrill

Reputation: 13

ARRAYFORMULA version of a formula produces different results than the regular formula

I've included a link to the Google Sheet below, in the spreadsheet column E has the formula:

=choose(iferror(match($A2,{"Cancelled","Closed","Pending Approval", "Billed"},0),5), "Closed", "Picked Up", "Entered", "Picked Up", if($B2="Yes", if($C2="- None -","Processing" ,"Ready"), "Approved"))

to evaluate some order stats data and the results are as expected. Column D has an ARRAYFORMULA version of the same formula:

=array_constrain(arrayformula(choose(iferror(match($A$2:$A,{"Cancelled","Closed","Pending Approval", "Billed"},0),5),"Closed", "Picked Up", "Entered", "Picked Up", if($B$2:$B="Yes",if($C$2:$C="- None - ","Processing" ,"Ready"),"Approved"))),counta($A$2:$A),1)

in the arrayformula version the IF statement always returns 'false' and evaluates to "Ready".

I want to use arrayformula to make the results dynamic as the order data is updated frequently and the number of rows changes.

While I suspect a bug, the formula was working correctly until recently, can anyone assist with getting this to work as expected?

Reference

https://docs.google.com/spreadsheets/d/1De8hffSvhDAvB3A4DjWre44r8DVEcwqNn5ToXriLjc8/edit?usp=sharing

Upvotes: 0

Views: 163

Answers (1)

doubleunary
doubleunary

Reputation: 18733

That seems a bit odd indeed. Try handling the "Pending Fulfillment" case first:

=arrayformula( 
  if( 
    A2:A = "Pending Fulfillment", 
    if( 
      B2:B = "Yes", 
      if(regexmatch(C2:C, "None"), "Processing", "Ready"), 
      "Approved" 
    ), 
    choose( 
      iferror( 
        match( 
          A2:A, 
          { "Cancelled", "Closed", "Pending Approval", "Billed" }, 
          0 
        ), 
        5 
      ), 
      "Closed", "Picked Up", "Entered", "Picked Up", iferror(1/0) 
    ) 
  ) 
)

For simplicity and easier maintenance, you may want to do this sort of thing using vlookup() with a lookup table in a helper sheet.

Upvotes: 1

Related Questions