-1

I am trying to build a filter functionality within Spreadsheets itself.

The sheet looks as follows: enter image description here

The function looks as follows:

=
IF(and(I4="ALL", I6="ALL", I8="ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G ", 2),

IF(and(I4<>"ALL", I6="ALL", I8="ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" ", 2),
IF(and(I4="ALL", I6<>"ALL", I8="ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where G="""&I6&""" ", 2),
IF(and(I4="ALL", I6="ALL", I8<>"ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where H="""&I8&""" ", 2),
IF(and(I4="ALL", I6="ALL", I8="ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where J="""&I10&""" ", 2),

IF(and(I4<>"ALL", I6<>"ALL", I8="ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and G="""&I6&""" ", 2),
IF(and(I4="ALL", I6<>"ALL", I8<>"ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where G="""&I6&""" and H="""&I8&""" ", 2),
IF(and(I4="ALL", I6="ALL", I8<>"ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where H="""&I8&""" and J="""&I10&""" ", 2),
IF(and(I4<>"ALL", I6="ALL", I8="ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and J="""&I10&""" ", 2),

IF(and(I4<>"ALL", I6="ALL", I8<>"ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and H="""&I8&""" ", 2),
IF(and(I4="ALL", I6<>"ALL", I8="ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where G="""&I6&""" and J="""&I10&""" ", 2),

IF(and(I4<>"ALL", I6<>"ALL", I8<>"ALL", I10="ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and G="""&I6&""" and H="""&I8&""" ", 2),
IF(and(I4="ALL", I6<>"ALL", I8<>"ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where G="""&I6&""" and H="""&I8&""" and J="""&I10&""" ", 2),

IF(and(I4<>"ALL", I6="ALL", I8<>"ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and H="""&I8&""" and J="""&I10&""" ", 2),
IF(and(I4<>"ALL", I6<>"ALL", I8="ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and G="""&I6&""" and J="""&I10&""" ", 2),

IF(and(I4<>"ALL", I6<>"ALL", I8<>"ALL", I10<>"ALL"), query(Vastgoedbeheerders!1:1000,"select A, B, C, D, E, F, G where C="""&I4&""" and G="""&I6&""" and H="""&I8&""" and J="""&I10&""" ", 2)))))))))))))))))

For some reason it results into:

Error Array result was not expanded because it overwrites data in A5.

I don't know what to change in the function in order for it to get it working correctly, any help would be appreciated!

1

The error message means that your formula returns an array of values but it can't be shown because the required cells aren't empty.

One alternative is to use a function like INDEX or ARRAY_CONSTRAIN to limit the results.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.