How to use ARRAYFORMULA in an IFS statement

Question

Using IFS with ARRAYFORMULA creates an error saying that the: "IFS has mismatched range sizes. Expected row count 1. column count 1......"

=IFS(A2="A",ARRAYFORMULA(B3:B10),A2="B",ARRAYFORMULA(C3:C10) create an error on my test page below. Any help is appreciated.

https://docs.google.com/spreadsheets/d/1FsacWMMCptyy0YU_ZwC6d0TUzbeUeIlCzRUTF-gSFcU/edit?usp=sharing

Answer 1

The ifs() function is more picky than the if() function when it comes to operand and result dimensions. Use this pattern:

=arrayformula( 
  ifs( 
    iferror(B3:B10 / 0, A2) = "A", 
      B3:B10, 
    iferror(C3:C10 / 0, A2) = "B", 
      C3:C10, 
    true, 
      na() 
  ) 
)