Search code examples
google-sheetsgoogle-sheets-formula

Regex Matching other values if input value is blank or not found in the range, in Google sheets


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

Sample File

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

Solution

  • 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&"'"))