Array arguments to IF are of different size

Question

I am currently working with data collected from google forms and am using google sheets. I would like to use ArrayFormula with the following, but an unable to do so, and receive the error message "Array arguments to IF are of a different size". I am referencing cells from the current sheet that I am working in (F2:F for example), and another sheet in the same workbook ('Form Responses 1'!L2:L for example). The arrayformula function seems to work with other IFS formulas I have on this sheet, but not this one. Please help!

=ArrayFormula(IFS(F2:F="","",F2:F="Married student with no children","",F2:F="Married student with children","",(F2:F="Single Student")('Form Responses 1'!L2:L="5th"),"PRTS Apt+1",(F2:F="Single Student")('Form Responses 1'!M2:M="5th"),"Big House",(F2:F="Single Student")('Form Responses 1'!N2:N="5th"),"Manor Apt",(F2:F="Single Student")('Form Responses 1'!O2:O="5th"),"Manor+1",(F2:F="Single Student")*('Form Responses 1'!P2:P="5th"),"Other"))

At a glance, all I can tell you is that formula does not follow standard formula-writing structure at all. Beyond that, keep in mind that we can't see your spreadsheet. So we don't know your layout, data, data types, data scope, range inclusions, etc. So there isn't much help the volunteer contributors here can offer currently. Consider sharing a link to your spreadsheet (or to a copy of it), where you have *hand-entered* some results for several columns (where the formula should produce those results). — Erik Tyler, Feb 18 '22 at 19:17
Thank you! This is my first time here, and I am grateful for an answer. Writing excel/google sheet formulas is a rare thing for me, so I just followed some tutorials on YouTube. Here is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1WpR5z2ocRGv_UJCDIR0mBI8IxYNIl-GrfFlaun9ZgdY/edit?usp=sharing / IN 'Housing Requests Responses' I want the columns to automatically fill down as data is entered via a google form in 'Form Responses 1' -- I achieved this is all columns except J, K, L, M, N using arrayformula. Thank you again for your help! — Kim49525, Feb 21 '22 at 11:35

score 0 · Answer 1 · answered Feb 21 '22 at 17:38

You'd better have all the ranges ARRAYFORMULA iterates over on a single sheet. Otherwise you'll have to make sure you have the same number of rows on both of them, or use a lot of OFFSET or ARRAY_CONSTRAIN to adjust them to the minimum number.

You could try something like this:

=ArrayFormula(
  IFS(
    F2:F = "",,
    F2:F = "Married student with no children",,
    F2:F = "Married student with children",,
    F2:F = "Single Student",
      IFS(
        ROW(F2:F) > ROWS('Form Responses 1'!A:A),,
        VLOOKUP(ROW(F2:F), {SEQUENCE(ROWS('Form Responses 1'!A2:A), 1, 2), ROWS('Form Responses 1'!L2:L)}, 2,) = "5th",
          "PRTS Apt+1",
        VLOOKUP(ROW(F2:F), {SEQUENCE(ROWS('Form Responses 1'!A2:A), 1, 2), ROWS('Form Responses 1'!M2:M)}, 2,) = "5th",
          "Big House",
        VLOOKUP(ROW(F2:F), {SEQUENCE(ROWS('Form Responses 1'!A2:A), 1, 2), ROWS('Form Responses 1'!N2:N)}, 2,) = "5th",
          "Manor Apt",
        VLOOKUP(ROW(F2:F), {SEQUENCE(ROWS('Form Responses 1'!A2:A), 1, 2), ROWS('Form Responses 1'!N2:N)}, 2,) = "5th",
          "Manor+1",
        VLOOKUP(ROW(F2:F), {SEQUENCE(ROWS('Form Responses 1'!A2:A), 1, 2), ROWS('Form Responses 1'!N2:N)}, 2,) = "5th",
          "Other"
      )
  )
)

I'm sure there is a simpler solution, but you have not provided any access to your sample sheet.

Array arguments to IF are of different size

1 Answers1