Finding Rows in Google sheets that CONTAIN a lookup value (NO CODE)

  • 3 March 2021
  • 3 replies
  • 3647 views

Userlevel 7
Badge +12

 

Hi there!


I have seen this requested a couple of times so thought I would try to tackle a solution. This is what I would consider advanced but does not include any code blocks or coding. In this example, I will be using days of the week but the solution could be modified for any value. 

Step 1: Create a Column on your Google Sheet which will ‘act’ as the look up column.

in the top most cell of your newly created lookup column, input the formula =ArrayFormula(IF(ISBLANK(A2:A),"",IF(REGEXMATCH(A2:A, "{INSERT LOOK UP VALUE}"), 1, 0)))

In my example below, I am am currently looking for the value “Monday” in Column A. 

where you see the values A2:A should be changed to whatever column you actually want to search for your value. 
​​​​

 

Step 2: MAKING IT DYNAMIC (can skip this step if you will always be looking for the same value)

In Zapier, you will add an update spreadsheet row action. You should update the row that contains the formula, in most instance this will probably be row 2. You only need to update the Lookup Column cell and you will update with the formula above but now you can dynamically paste in your lookup value from a previous step. Here, we are changing are lookup value from “Monday” to “Wednesday”

 Note that, testing the action changed my formula and now I am lookup in Column A for cells that contain “Wednesday”

 

Step 3: Adding the Find Many Spreadsheet Rows (With Line Item Support) in Google Sheets action. 

Add the find many spreadsheet rows action to your zap, configure it to look at the Lookup Column searching for the value 1.

Upon testing, you will see that Zapier has just matched all of the values that had a 1 (which matches all of the values in Column A that CONTAIN “Wednesday” 

 

 

There you have it! Proceed with the rest of your Zap set up!


3 replies

Userlevel 7
Badge +12

This is awesome, thanks for sharing @GetUWired

Amazing! Thank you!

Userlevel 7
Badge +8

Nice!! Thanks for sharing this @GetUWired :clap_tone3: cc @nicksimard 

Reply