regexgoogle-sheetsgoogle-sheets-formula

Regex Match a string or any other "greater value" string in Google Sheets


in Google Sheets, I have a table where one of the columns has the following format as the table below (actually the pattern is not separated by "//" like the example, but with a linebreak, which I wasn't able to replicate in this text table... but anyways):

Position/Experience/Specialty
Position: Cook, Experience: 1 to 3 months, Specialty: Bistro // Position: Cashier, Experience: 1 to 3 months, Specialty: Bistro // Position: Dishwasher, Experience: 3 months to 1 year, Specialty: Seafood
Position: Cheff, Experience: Not experienced, Specialty: Seafood // Position: Cashier, Experience: 1 to 3 months, Specialty: Bistro
Position: Dishwasher, Experience: 1 to 3 years Specialty: Seafood
Position: Dishwasher, Experience: 1 to 3 years, Specialty: Bistro // Position: Cook, Experience: 1 to 3 months, Specialty: Bistro

My objective is that by inputting the 3 variables, I can find the rows that match what I am looking for. (and this has been partially accomplished)

This is the sample input table i'm using:

Variable Input
Position
Experience
Specialty

The problem happens with the variable "Experience". Whenever I input experience ,for example, 1 to 3 months, I would like the formula to also consider higher experience, like 1 to 3 years, etc. In this case it exactly matches the ones that have 1 to 3 months. But because this is String, I cannot find any "greater values" than 1 to 3 months as well. What would be the best approach?

The formula im using:

=filter('Registration Form'!A2:V,REGEXMATCH('Registration Form'!L2:L,CONCATENATE(if(B2="","",CONCATENATE("Position: ",B2,", ")),if(B3="","(.*?)",CONCATENATE("Experience: ",B3,", ")),if(B4="","",CONCATENATE("Specialty: ",B4)))))

You can find the sample file where it will be easier to understand:

Sample File

Thanks


Solution

  • I created a range in Z:Z with the possible values of experience, and how they're sorted (from lower to higher). Then I just joined the different options of REGEXMATCH for each of the three cells. In the case of experience, it would use TEXTJOIN and FILTER to concatenate all the possible options according to the matching of the value (and their consequent values):

    =filter('Registration Form'!A2:V,
    REGEXMATCH('Registration Form'!L2:L,B2)*REGEXMATCH('Registration Form'!L2:L,TEXTJOIN("|",1,FILTER(Z:Z,ROW(Z:Z)>=IFNA(XMATCH(B3,Z:Z),0))))*REGEXMATCH('Registration Form'!L2:L,B4))
    

    enter image description here