Hi there!
Tim here from the Zapier Support Team with a workflow idea.

Background

A common thing to do in Zapier is to search for a row in a spreadsheet based on some value. For example, we could search for a customer by their name or email address. But what if what if our search data is only a fragment of what is stored in our spreadsheet, but we still want to find the row. 

For example, what if we want to search a set of names but we sometimes we have a full name to search with and sometimes we don’t? What if the capitalization doesn’t perfectly match? 

We can successfully find the row by doing a “Fuzzy Search”. A Fuzzy Search in this case is where we look for instances where the search term and cell don’t exactly match, but the cell contains the search term.

This isn’t supported in the Google Sheets App (we are tracking requests for this feature for Sheets, so let us know via our Support Contact Form if you want that). Google Sheets will only return exact matches for now.

I was able to find a way to use Airtable’s Search Formulas to do a Fuzzy Search in an Airtable. To use this method, you’d need to use Airtable to store your “spreadsheet” information. Airtable is a database app that has an interface that feels much more like a spreadsheet, so if you’re familiar with Google Sheets it should be relatively easy to pick it up.

Example

Let’s say I have the following data in my Airtable:

In this case, the Full Name column is defined by a formula in Airtable that combined the First and Last Name columns, but that isn’t necessary. If you wanted to search in multiple columns in each record, you could create a formula that combines the values from multiple columns into one and then do the Fuzzy Search in that combined column.

Let’s say we get a form submitted by Zaphod from another app but they only identified themselves as Beeblebrox. We can set up the Airtable “Find Record” Step like this:

We’re using the Airtable FIND formula to look for the mapped value (Beeblebrox) inside the “Full Name” column. 

We’re mapping the search term in between the <TriggerValue></TriggerValue> tags and typing in the Column Name we want to search between the {curly braces}. Note that we want to leave the Search by Field and Search Value fields blank, and only map the Search Formula Field.

If we test the Find Step, we can see that even though the Full Name field wasn’t an exact match, it found the right record because it contained the search term:

Let’s try another one. This time, Trillian submits the form, but she typed her name as lowercase. If we were testing this in the Zap Editor, the formula field would look like this:

The UPPER formula that is nested inside the FIND formula changes both the input value and the values in the column we’re searching for to all uppercase (just for the purposes of the search, the records won’t change in the Airtable). That means we are able to resolve the casing mismatch and find the record:

Other Use Cases

Another use case for a search like this would be to check if any email addresses exist for a specific domain. 

Let’s say we have a list of records with emails like this:

We get an email from dolphin@galaxy.com and we want to see if we already have any email address from that domain (@galaxy.com) in our Airtable. We can do that by: 

• Splitting the email address with the Formatter by Zapier App’s Text Action and the Split transform on the “@“ symbol.

• 

  

   

• Using the fuzzy search formula to search for “galaxy.com” part that we split out in our Airtable

• 

   

This would return the record with “zaphod@galaxy.com” so we’d know we already have a contact from that domain in our list:

I’ve also seen users who have multiple values in one cell of a spreadsheet. They will get one value from a Trigger and want to find the row that contains that single value to get the corresponding information in the row. For example, multiple Zip codes in Column A, and you want to search for a single Zip code that might be grouped with others and return the info in the other columns for the row that has the Zip code you’re checking in Column A.
This particular problem could be solved more directly with a database app like Airtable, MySQL, etc, too, but if you’re not ready to make that leap yet, this could be a workable alternative.

Here’s the formula to copy and paste: 

FIND(UPPER("<TriggerValue>{{Map Your Search Value Here}}</TriggerValue>"),UPPER({Type your column name here}))

Note:

• Don’t remove the curly braces from the column name section.
• The curly braces for the “Map Your Search Value Here” text will turn look like a Zapier mapped value when pasted into the Formula field. Delete that value and map in your own search term.

Give it a try and let me know what you think :)

Let me know if you have any questions about this and if you have a different use case for this kind of search, please share below to help your fellow Zapier users find other possibilities for this kind of search!