filter from filtered array

[tminhv]

I'm trying to create a filter based on a filtered array as the conditions. Trying to get =FILTER('location contents'!$E:$E,'location contents'!$A:$A=FILTER('cut history'!$E:$E,('cut history'!$K:$K=B36)*('cut history'!$L:$L=A36)*('cut history'!$A:$A=G36),""),"") to work.

 

[djclements]

It looks like you are attempting to filter by a list of values, in which case you can try using ISNUMBER-XMATCH. For example:

=LET(
    lookup_arr, FILTER('cut history'!$E:$E,('cut history'!$K:$K=B36)*('cut history'!$L:$L=A36)*('cut history'!$A:$A=G36),""),
    FILTER('location contents'!$E:$E,ISNUMBER(XMATCH('location contents'!$A:$A,lookup_arr)),"")
)

I'm not a huge fan of referencing entire worksheet columns, as it can affect overall performance. It would be better to limit the references to a reasonable amount of rows in relation to your dataset.

Having said that, you could also SORT the lookup_arr, then set the optional [search_mode] argument of XMATCH to 2 - binary seach (sorted ascending order) to help speed things up:

=LET(
    lookup_arr, SORT(FILTER('cut history'!$E:$E,('cut history'!$K:$K=B36)*('cut history'!$L:$L=A36)*('cut history'!$A:$A=G36),"")),
    FILTER('location contents'!$E:$E,ISNUMBER(XMATCH('location contents'!$A:$A,lookup_arr,,2)),"")
)

Hopefully that's what you were aiming for.

 

[tminhv]

It looks like you are attempting to filter by a list of values, in which case you can try using ISNUMBER-XMATCH. For example:

=LET(
    lookup_arr, FILTER('cut history'!$E:$E,('cut history'!$K:$K=B36)*('cut history'!$L:$L=A36)*('cut history'!$A:$A=G36),""),
    FILTER('location contents'!$E:$E,ISNUMBER(XMATCH('location contents'!$A:$A,lookup_arr)),"")
)

I'm not a huge fan of referencing entire worksheet columns, as it can affect overall performance. It would be better to limit the references to a reasonable amount of rows in relation to your dataset.

Having said that, you could also SORT the lookup_arr, then set the optional [search_mode] argument of XMATCH to 2 - binary seach (sorted ascending order) to help speed things up:

=LET(
    lookup_arr, SORT(FILTER('cut history'!$E:$E,('cut history'!$K:$K=B36)*('cut history'!$L:$L=A36)*('cut history'!$A:$A=G36),"")),
    FILTER('location contents'!$E:$E,ISNUMBER(XMATCH('location contents'!$A:$A,lookup_arr,,2)),"")
)

Hopefully that's what you were aiming for.

It works perfectly! Thank you so much for your help.