I've been using array formulas in my google Sheets a lot, of the type:

={"Heading";arrayformula(if(len(A2:A)>0,FORMULA HERE,""))}

in order to "auto drag down" a formula when the A column is populated.

I would normally use index,match syntax of the type:

=index("what I want returned",match(1,(criterion1=lookuprange1)*(criterion2=lookuprange2),0))

However, those don't seem to work when inserted in the array formula above. I've found a workaround in doing a regular vlookup and creating a concatenated criteria and range but I prefer index,match so any help on making it work or an understanding why it won't work in the array formula are welcome.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.