How to use ranges from 2 sheets within Filter function

Question

@Player0 helped me with my original question on getting this ArrayFormula set up. I am now needing to add additional ranges to the Filter functions. I thought using Concat would do the trick but it's just repeating values and not giving me the desired output.

Original Formula:

=ARRAYFORMULA(REGEXREPLACE(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(IF(
            FILTER('OFF FIGURE'!D11:Z184, MOD(COLUMN('OFF FIGURE'!D11:Z184) , 5)=0)=C11, 
            FILTER('OFF FIGURE'!E7:Z184, MOD(COLUMN('OFF FIGURE'!E7:Z184)+1, 5)=1), ),,9^9)), 
            "(?i) "," " ), "  ", ", "), "^$", "OPEN"))

Desired Formula:

=ARRAYFORMULA(CONCAT(REGEXREPLACE(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(IF(
            FILTER('OFF FIGURE'!D11:Z182, MOD(COLUMN('OFF FIGURE'!D11:Z182) , 5)=0)=C7, 
            FILTER('OFF FIGURE'!D7:Z178, MOD(COLUMN('OFF FIGURE'!D7:Z178)+1, 5)=1), ),,9^9)), 
            "(?i) "," " ), "  ", ", "), "^$", "OPEN"),
 ARRAYFORMULA(REGEXREPLACE(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(IF(
            FILTER(FASHION!D12:Z183, MOD(COLUMN(FASHION!D12:Z183) , 5)=0)=C7, 
            FILTER(FASHION!E7:Z178, MOD(COLUMN(FASHION!E7:Z178)+1, 5)=1), ),,9^9)), 
            "(?i) "," " ), "  ", ", "), "^$", "OPEN"))))

Desired outcome would be to get the values from both the Off Figure AND Fashion sheets. Obviously Concat is not what I want...

EDITS

I added the formula on this spreadsheet and got pretty much the same results I did when I tried using the Concat function.

Link to test spreadsheet: https://docs.google.com/spreadsheets/d/1d0SXXQxbd9mPQSjr6olDIzVdL6Gg8FWZtA9oVDQx9bo/edit#gid=1966451697

user_5632629 · Answer

try:

=ARRAYFORMULA(REGEXREPLACE(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY({IF(
 FILTER(FASHION!D12:Z183, MOD(COLUMN(FASHION!D12:Z183) , 5)=0)=C3, 
 FILTER(FASHION!E7:Z178, MOD(COLUMN(FASHION!E7:Z178)+1, 5)=1), ); IF(
 FILTER('Off Figure'!D12:Z183, MOD(COLUMN('Off Figure'!D12:Z183) , 5)=0)=C3, 
 FILTER('Off Figure'!E7:Z178, MOD(COLUMN('Off Figure'!E7:Z178)+1, 5)=1), )},,9^9)), 
 "(?i) "," " ), "  ", ", "), "^$", "OPEN"))

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(TRIM(QUERY({IF(
 FILTER(FASHION!D12:Z183, MOD(COLUMN(FASHION!D12:Z183), 5)=0)=C5, 
 FILTER(FASHION!D6:Z177, MOD(COLUMN(FASHION!D6:Z177)+1, 5)=0), ); IF(
 FILTER('Off Figure'!D12:Z183, MOD(COLUMN('Off Figure'!D12:Z183), 5)=0)=C5, 
 FILTER('Off Figure'!D6:Z177, MOD(COLUMN('Off Figure'!D6:Z177)+1, 5)=0), )},,9^9)), 
 "(?i)bay ", ), " ", ", "))

How to use ranges from 2 sheets within Filter function

1 Answers