Google-sheets – Parse error with nested IFs in an array formula using SUMPRODUCT and VLOOKUP

google sheetsworksheet-function

I am trying to create a nested IF formula to calculate the value of a VLOOKUP-ed range of cells in a separate spreadsheet using if, arrayformula, sumproduct, vlookup, and importrange. There are three possible results in the nested if, based on the value of cell F3 (blank, "bar", and "foo"). Here's the code I have so far: 

=if(isblank(F3), "",
if(F3="bar", 
ArrayFormula(sumproduct(vlookup(A3, importrange("external link", "Form Responses 1!D:BA"),{2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40},0))), 
if(F3="foo", ArrayFormula(sumproduct(vlookup(H3, importrange("external link", "Form Responses 1!D:BA"){2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40},0))))))

(bar and foo determine the cell VLOOKUP uses as a search key).

The array formula works fine by itself, it is only when I start adding any IFs that the output becomes "#ERROR!" (formula parse error).

Does anyone have any suggestions for how to integrate this nested if and array formula?

Best Answer

Your first vlookup looks like

vlookup(something, importrange(blah, blah) , {2,4,6,…,40}, 0)

but your second one looks like

vlookup(something, importrange(blah, blah)   {2,4,6,…,40}, 0)

i.e., it appears to be missing a comma before the {.

P.S. You can simplify that horribly long formula by using

if(F3="bar", A3, if(F3="foo", H3, <something_else>))

as in

vlookup(
       if(F3="bar", A3, if(F3="foo", H3, <whatever>)) ,
                                                     importrange(blah, blah),{2,4,6,…,40},0)

instead of repeating the ArrayFormula(sumproduct(vlookup(…))) expression twice.

Related Topic