Optimizing code for Name database in Google Sheets-CodePudding

I looking for help to optimize the code for a Name database I created in Google Sheets. I'm not a programmer and I have made this by researching on Youtube, Stack Overflow and other sites.

The data sheets now work as intended, but the code is way too long and it takes time for the sheet to load up and gets laggy sometimes. The database is basically a tag filter system where is excludes any name that doesn't include all the tags selected.

This is the link to the Google Sheet:

https://docs.google.com/spreadsheets/d/1g1GjgVzABXHDOsxCQS3x9F3lgeIjyxnZggbDcZj04AM/edit?usp=sharing

The code I need to optimize is in the "Search" tab in W3, X3, Y3, Z3, AA3. Thanks!

It's been a while since I have seen the code so I don't remember well. There was a function(s) I tried that would compact all the cells from a selected range, instead of writing each cell like in my code, but it did not work.

CodePudding user response：

Use filter() and regexmatch(), like this:

=lambda( 
  regex, 
  iferror( 
    filter( 
      'Master Data'!A3:F, 
      regexmatch( 
        'Master Data'!AX3:AX, 
        regex 
      ) 
    ), 
    "(no matching data)" 
  ) 
)( 
  textjoin( 
    "\b.*?\b", true, 
    sort( 
      flatten( 
        bycol( 
          sequence(1, columns(H2:U2) / 2, 0, 2), 
          lambda( 
            colOffset, 
            iferror( 
              filter( 
                offset(I3:I, 0, colOffset), 
                offset(H3:H, 0, colOffset) 
              ) 
            ) 
          ) 
        ) 
      ) 
    ) 
  ) 
)

Put the formula in cell Search Tab!W3. It will fill columns W:AB automatically.