Google Sheets - Arrayformula referencing other sheets equals broken formula

Question

I have a spreadsheet with a few sheets I reference with =INDEX. The code I use below works fine when I Paste as Formula to all the cells in a column.

=INDEX(DATABASE!A:A, MATCH('SMS FINAL'!C2:C, DATABASE!C:C, 1))

The code I am trying to use has ArrayFormula so that I only have to type the formula in once:

=ArrayFormula(
 IF(ROW(E:E)=1,"Company",
  IF(ISBLANK(E:E),"",
  INDEX(DATABASE!B:B, 
  MATCH('SMS FINAL'!C:C, DATABASE!C:C, 1)
))))

This breaks the column and only leaves the "Company" in the header row. Any ideas?

player0 player0 · Accepted Answer · 2019-03-15T00:24:22

try it like this:

=ARRAYFORMULA({"Company";
  IF(ISBLANK(E:E), ,
  INDEX(DATABASE!B:B, 
  MATCH('SMS FINAL'!C:C, DATABASE!C:C, 0)))})

or like this:

=ARRAYFORMULA({"Company";
  IF(ISBLANK(E2:E), ,
  INDEX(DATABASE!B:B, 
  MATCH('SMS FINAL'!C:C, DATABASE!C:C, 0)))})

Google Sheets - Arrayformula referencing other sheets equals broken formula

1 Answers