I have a data set listed in column A on another sheet called data. On another sheet I am using below formula to populate a cell when the user types in cell B1. I would like to populate only the filtered data. So far this works well if I search the text in the right order, but won't if the order is reversed. Can anyone help to incorporate ^(?=.*\bCream\b)(?=.*\bBetaderm\b).*$ in the formula to make it work?

=ArrayFormula(filter(data!A2:A,(regexmatch(lower(data!A2:A),lower(substitute(B1," ", ".*"))))))

The list of data is really long part of it is:

Camphor USP Crystal <= 2%   Menthol <= 2% in Betaderm (Betamethasone Valerate) Cream 0.1%
Camphor USP Crystal <= 2%   Menthol <= 2% in Betaderm (Betamethasone Valerate) Ointment 
Clindamycin HCl USP Powder <= 5% in Betaderm Cream 0.1% or 0.05%
Clindamycin HCl USP Powder <= 5% in Glaxal Base Cream

CodePudding user response：

Google Sheets uses RE2 regexes that by design do not support backreferences and look-around assertions.

To get rows where both of the two space-separated words in cell B1 appear in any order, use this pattern:

=iferror( 
  filter( 
    data!A2:A, 
    iferror( regexmatch( data!A2:A, "(?i)" & regexextract( trim(B1), "^(\w )" ) ), true ),  
    iferror( regexmatch( data!A2:A, "(?i)" & regexextract( trim(B1), "(\w )$" ) ), true ) 
  ), 
  "(no row matches '" & trim(B1) & "')" 
)

In the event cell B1 only contains one word, the formula will get rows that match that word.

If you need to match more than two words, you will to add more criteria in the filter().

Note that \w will only match [a-z0-9]. If you need to match patterns like <= or 2%, you will have to modify the regexextract().

CodePudding user response：

You can use QUERY instead:

=ARRAYFORMULA(QUERY(data!A:A,"where lower(A) contains '"&JOIN("' and lower(A) contains '",SPLIT(LOWER(B1)," "))&"'",1))

• where to filter out rows
• contains to compare strings
• SPLIT and JOIN to create dynamic strings of where Col1 contains {string} according to the number of words in B1