I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set of tags (that have their own column in D2:D). There is a master "All Publications" tab and using the filter function I check each cell in the 'Primary Category' column ('All Publications'!B5:B) and if it matches one of the cells in the 'Tags for Tab' column that article is included. Example: I might want all articles with a primary category of "Rot" to be included under a new sheet called 'Rot Articles'. I have this functionality in place already with the following filter:
=FILTER('All Publications'!A5:A, REGEXMATCH(LOWER('All Publications'!B5:B), LOWER(TEXTJOIN("|", TRUE,D3:D))))
D3:D is the column where the tags for the given sheet are kept. I now have two additional columns called 'Secondary Category' ('All Publications'!C5:C) and 'Tertiary Category' ('All Publications'!D5:D) respectively.
I want to be able to include any article that has the keyword 'Rot' in any of the three categories (primary, secondary, tertiary). The query above only checks the primary category. In other words: I want an article to populate my sheet if a match for any of the tags in 'Rot Articles'!D3:D is found in the ranges
'All Publications'!B5:B OR 'All Publications'!C5:C OR 'All Publications'!D5:D. How do I do this? Its important to know that these articles contain hyperlinks and so using the QUERY function is not an option.
Anyone that wants to play with this toy example is free to access the sheet here: https://docs.google.com/spreadsheets/d/1MOxisNd_Nw-_YKHJHCbi1qWvoVh3K8Tu9xxAxee3520/edit?usp=sharing