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:
Thanks
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))