In Google Sheets, I have data from a registration form in Tab 1. In Tab 2, im trying to get an array of results according to multiple filters.
The problem is with one of the filters where the field data is stored this way in each cell:
"Position: Cook, Experiencia: 1 to 3 months, Specialty: Bistro
Position: Cashier, Experiencia: 1 to 3 months, Specialty: Bistro
Position: Dishwasher, Experiencia: 3 months to a 1 year, Specialty: Seafood"
And then I can have another response as this:
"Position: Cashier, Experiencia: 3 to 5 years, Specialty: Seafood"
So depending on each form response, the values that they input for each variable(Position,Experiencia,Specialty) may change, as well as the numbers of lines.
Objetive: Being able to Filter and RegexMatch according to what ever value I input for each variable (even if it is blank),
What I have accomplished so far is concatenating the 3 the fields I want to look up and then doing a RegexMatch. The formula successfully finds the row where the 3 variables are a match.
Problem: Whenever the field in the middle is blank, the formula breaks because it concatenates something it won't find. But still, I need it to look up even if this field is blank, just considering every other option.
This is the formula that I'm currently using. As you can see, it uses data from form submissions in the specified column ('Registration Form'!L2:L), to RegexMatch it with a CONCATENATE of 3 fields where it gets the same format, and IF any of those 3 fields is blank, it replaces the field with "".
This is the formula:
=filter('Registration Form'!A2:V, REGEXMATCH('Registration Form'!L2:L,CONCATENATE(if(B7="","",CONCATENATE("Position: ",B7,", ")),if(B8="","",CONCATENATE("Experiencia: ",B8,", ")),if(B9="","",CONCATENATE("Specialty: ",B9)))))
And this is a sample of how I input the data that it has to filter
Variables | Input |
---|---|
Position | Cashier |
Experiencia | 1 to 3 months |
Specialty | Bistro |
Here's another solution:
=QUERY('Registration Form'!A2:V,
"where 0=0"&
IF(B2="",," and L contains '"&B2&"'")&
IF(B3="",," and L contains '"&B3&"'")&
IF(B4="",," and L contains '"&B4&"'"))