Question

Find all partial matches for lookup of Google Sheets rows

  • 8 December 2021
  • 6 replies
  • 647 views

I have a Google Sheet full of country codes (of various sizes) and am looking to create a Sheets lookup to find what country a clients’ number falls under.

In my search, I have found solutions for finding exact matches - but not partial.

Since the country codes are of various lengths, I cannot truncate the clients’ number and expect a match - as it might be eating into the country code.

Therefore, is there a way where I can use Zapier to search if the country code is contained within a clients’ phone number?

Been running into a few issues trying it out, as it seems the “Lookup spreadsheet row” in Sheets is only for exact matches.


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

I’ve also found the “Lookup Many” rows - but the max limit is 20. 

My spreadsheet has 200+ rows & I want to keep my Zap as efficient as possible (i.e. I dont want to do a lookup 10 different times to fulfil the 200+ rows)

Userlevel 7
Badge +14

Hi @Daniel Galea 

Confirming the GSheets Lookup Row(s) Zap actions work on exact matches.

 

Are you able to provide a few different examples for us to have more context about the input data and expected output data (match)?

 

You may want to consider using Airtable instead of GSheets: https://zapier.com/apps/airtable/integrations

Airtable has a Find Record Zap action that can use an advanced Search Formula: https://support.airtable.com/hc/en-us/articles/218151657

 

Please see attached list of country codes I have put in Airtable.

If I have a clients’ phone number, e.g. starting country code +93 (Afghanistan) - I want to search the whole Airtable and find which Country it relates to.

However on Zapier, this is difficult to do - as some country codes are longer than others, i.e. I cannot truncate the clients’ phone number.

Hence the partial match, yet I cannot see how to search for a partial match

E.g. search spreadsheet for “+5512992”, and +55 would come up as a partial match & output the country

So it’s kind of doing a backward search - i.e. not searching the spreadsheet for a country code, but the whole value & outputting a partial match that it fits under

Userlevel 7
Badge +14

@Daniel Galea 

How do you intend on handling countries that share the same country code?